Month on Month value for This Year and Previous Year

[powerrdl]

The above image shows a matrix with 'Month' in Rows, 'Year' in Columns and 'Policy Count' in Values.

I need to write DAX Expressions for 'Policy Count TY' and 'Policy Count PY' such that I get same output when I create a matrix with just 'Month' in Rows and the two Expressions, and no filter applied on Year Slicer. Expressions should be able to take Max(Year) and Max(Year)-1.

Further, if I select, say, Nov in Month Slicer, the Matrix is required to show all months upto Nov and not just Nov. Values against each month are required as 'for the month' and not cumulative.

I have tried various options but no luck. I am either getting 'Contiguous Dates' error or getting cumulative output

Thanks

[Eric_Zhang · Accepted solution]

@powerrdl

You can see a workaround by using two extra auxiliary tables. See more details in the attached pbix file.

By the way, may I know any reason not using a Matrix?

[powerrdl]

Hi @Eric_Zhang,

Many thanks for your solution ! Its a cool workaround !!

Regards

Rameen

[Anonymous]

Your life (or at least data model) will improve if you add a calendar table.

http://tinylizard.com/power-bi-date-table/ 

This will allow :

  Policy Count PY := CALCULATE([Policy Count], SAMEPERIODLASTYEAR(Dates[Date]))