Power BI Matrix to show Total and Difference

[sahildoshi]

Hi All, I am working on a requirement where I need to show Month on Month sales in a matrix. Below is my input data.

User need to select the Financial Year and all the Months who fall under that year should get displayed based on data availability.

The catch is, along with MoM Sales, Total sales and Difference between latest and latest-1 month sales should get displayed.

I have put Month field is put under column section of matrix and column total is enabled. But how do I get the Difference between latest month and latest-1 sales dynamically. 

Please help me achieve it. @GilbertQ @amitchandak 

[v-eqin-msft · Accepted solution]

Hi @sahildoshi ,

Please follow the steps:

1. Assume your Month column is Text type, I add a new Date column:

2. Then create a measure:

Measure = 
var _max=CALCULATE( LASTDATE('Table'[Date]),ALLEXCEPT('Table','Table'[Region]))
var _pre= EDATE(_max,-1)
var _diff=CALCULATE(SUM('Table'[Amount]),FILTER('Table',[Date]=_max)) - CALCULATE(SUM('Table'[Amount]), FILTER('Table',[Date]=_pre))
return IF(ISINSCOPE('Table'[Date].[Year]),SUM('Table'[Amount]),_diff)

But since I used Date hierarchies, the header label could not be changed to Total and Diff seperately.

Here is the Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@sahildoshi , Use a separate month/ date table.

Create a date from the month

new column

date = datevalue("01-" & [Month])

then you can use time intelligence

measures

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Or have month rank column on YYYYMM or month start date

Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)

then measure

This Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[sahildoshi]

thanks for the quick reply @amitchandak , but when I have Jun-22 data also available, the output table should look like below.

Dynamically every month sales should get added. Total of every month should get calculated and Diff column should display difference of latest 2 months.