Percent Difference in Matrix

Question

Hi All,&nbsp;I have sum of all records in matrix and would like to get Percent Difference in Matrix. Provided the sample output below&nbsp;Thanks in Advance

amitchandak · Answer

Hope you are llok for MOM diff. make sure you have date table and try likeMOM % =
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&nbsp;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-with-Winners/ba-p/890814https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739 https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601&nbsp;

amitchandak · Answer

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

amitchandak · Answer

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&nbsp;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 · Answer

Hi&nbsp;amitchandak&nbsp;,&nbsp;Thanks for your reply.&nbsp;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.&nbsp;&nbsp;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?&nbsp;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.&nbsp;Thanks in Advance

anonymous · Answer

Hi&nbsp;amitchandak&nbsp;,&nbsp;It seems like last year sales is caculating that way.&nbsp;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.&nbsp;Also do we have any option for weekly data? same as QTD,YTD and MTD same as above?&nbsp;Thanks in Advance

Forum Discussion

Percent Difference in Matrix

6 Replies

Related content

Handle AverageX on different granularities matrix drilldown

Incorrect Total for Percent Change in Power BI Matrix Visual

How to add percent difference 'column' to matrix

Matrix Show Different Hierarchy Levels

Matrix Showing Difference Between Two Years