Percent Difference in Matrix

[Anonymous]

Hi All,

I have sum of all records in matrix and would like to get Percent Difference in Matrix. Provided the sample output below

Thanks in Advance

[amitchandak]

Hope you are llok for MOM diff. make sure you have date table and try like

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

return (divide([MTD Sales],[last MTD Sales]))-1) *100

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Hi @amitchandak ,

Thanks for your reply.

I tried as per your suggestion but for 2020 it is not working as expected. Apart from 2020 it is working fine for all the other 3 years(i..,e 2017,2018,2019). I am not sure why it is not working fine for 2020 i.e., latest year.

Also can we do this for weekly basis? I know we can achieve this for YTD,QTD and MTD (Year,Quarter and Month) but what about weekly comparison?

Also providing other details as below:

Formula which i have used:

I have created a time Dimension with the help of calculated table and that is what i am going to provide in Matrix.

Thanks in Advance

[amitchandak]

Can check which value is it giving wrong for 2020, sales or last year sales. by having two measure

[Anonymous]

Hi @amitchandak ,

It seems like last year sales is caculating that way.

I have Jan month data alone in 2020. So when we put DATESYTD function so it is calculating the Jan 2019 data alone and skipping all the other 11 months of data in 2019 since because in 2020 it only have Jan month data.

Also do we have any option for weekly data? same as QTD,YTD and MTD same as above?

Thanks in Advance

[amitchandak]

For complete month , year you can use end of month or end of year. Last year same week is 364 days behind . So just have week no or week start date in you time table and use 364 days behind measure for last year , 7 days for last week

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 MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

last Year Week Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,Day))
last Week Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,Day))

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH)) 
3 month back MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH)))
 


QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))



YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

[Anonymous]

Hi @amitchandak ,

Everything is working fine except weeknum. i have dec 2019 like 2019-48, 2019-49, 2020-50,2020-51,2020-52.

Here 48,49,50,51,52 denotes weeknum of 2019 year. i would like to coompare 52 with 51, 51 with 50 and so on.

if i put the below formula it is not showing correct output

last Week Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,Day))

 Dowe have something like weeknum to date comparison?

Thanks in advance