英文:
Matrix Data Showing Based on Year/Month Slicer Selection
问题
你好！
在我的报告中，我根据今天的日期（2023年3月7日）将未付金额分类为不同的时间段，例如“当前”、“1期”、“2期”、“3期”和“4期+”。为了提供一些背景，当前是指在本月内具有发帖日期的未付金额，而1期是指上个月具有发帖日期的金额，依此类推。
现在，我在我的报告中添加了一个年份和月份切片器，并将其设置为单选。我想知道是否可以根据所选的年份和月份显示未付金额。例如，如果我选择了2023年和1月，矩阵表应该显示2023年1月为“当前”，2022年12月为“1期”，2022年11月为“2期”，依此类推，并对每个期间的未付金额进行求和。
我将非常感激您提供的任何帮助或指导，以实现这个结果。
请查看我附上的pbix文件以供参考。
Pbix链接：https://drive.google.com/file/d/1jOZrjlTcDxJL3AQ15kBX8y4IYOGpoYIg/view?usp=sharing
英文:
Good day to you!
In my report, I have categorized outstanding amounts under different time periods such as Current, 1 period, 2 period, 3 period, and 4 period+ based on today's date (7 March 2023). To provide some context, Current refers to outstanding amounts with post dates in the current month, while 1 period refers to amounts with post dates in the previous month, and so on.
Now, I have added a year and month slicer to my report, and I have set it to single selection. I would like to know if it is possible to display the outstanding amounts according to the selected year and month. For instance, if I select Year 2023 and month January, the matrix table should show year 2023 Jan as 'Current', 2022 Dec as '1 period', 2022 Nov as '2 period' and so on, and sum the outstanding amount for each period.
I would greatly appreciate any help or guidance you can provide to achieve this result.
Please find my pbix file attached for your reference.
Pbix: https://drive.google.com/file/d/1jOZrjlTcDxJL3AQ15kBX8y4IYOGpoYIg/view?usp=sharing
答案1
得分: 0
有一个显示不同时期的表格相当简单。
此矩阵包含5个度量：
M0 =
VAR ym = EOMONTH(LASTDATE('Calendar'[Date]),0)
RETURN CALCULATE(SUM(Outstanding[Outstanding Amount]), ALL('Calendar'),
EOMONTH(Outstanding[Post Date],0) = ym)
M1 =
VAR ym = EOMONTH(LASTDATE('Calendar'[Date]),-1)
RETURN CALCULATE(SUM(Outstanding[Outstanding Amount]), ALL('Calendar'),
EOMONTH(Outstanding[Post Date],0) = ym)
...
M4 =
VAR ym = EOMONTH(LASTDATE('Calendar'[Date]),-4)
RETURN CALCULATE(SUM(Outstanding[Outstanding Amount]), ALL('Calendar'),
EOMONTH(Outstanding[Post Date],0) <= ym)
MTotal = CALCULATE(SUM(Outstanding[Outstanding Amount]), ALL('Calendar'))
如果需要在标题中包含月份，您可以在上方放置一些卡片，并使用以下度量之类的度量：
Month1 = FORMAT(EOMONTH(LASTDATE('Calendar'[Date]),-1), "mmm YYYY")
英文:
To have a table that shows the different periods is quite trivial
This matrix contains 5 measures:
M0 =
VAR ym = EOMONTH(LASTDATE('Calendar'[Date]),0)
RETURN CALCULATE(SUM(Outstanding[Outstanding Amount]), ALL('Calendar'),
EOMONTH(Outstanding[Post Date],0) = ym)
M1 =
VAR ym = EOMONTH(LASTDATE('Calendar'[Date]),-1)
RETURN CALCULATE(SUM(Outstanding[Outstanding Amount]), ALL('Calendar'),
EOMONTH(Outstanding[Post Date],0) = ym)
...
M4 =
VAR ym = EOMONTH(LASTDATE('Calendar'[Date]),-4)
RETURN CALCULATE(SUM(Outstanding[Outstanding Amount]), ALL('Calendar'),
EOMONTH(Outstanding[Post Date],0) <= ym)
MTotal = CALCULATE(SUM(Outstanding[Outstanding Amount]), ALL('Calendar'))
If you need to have the Months in the header, you can put some cards above and use some measure like this:
Month1 = FORMAT(EOMONTH(LASTDATE('Calendar'[Date]),-1), "mmm YYYY")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论