Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Percent Difference in Matrix

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
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

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-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      amitchandak
      Super User
      6 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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