CODEWITHSUNDEEP
powerbidax
Nick
Nick

Reputation: 81

Matrix Data Showing Based on Year/Month Slicer Selection

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.

enter image description here

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

Upvotes: 0

Views: 166

Answers (1)

Joao Leal
Joao Leal

Reputation: 5542

To have a table that shows the different periods is quite trivial

table

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")

Upvotes: 0

Related Questions