Forum Discussion

Katerina_e's avatar
Katerina_e
Frequent Visitor
9 years ago

YoY / MoM / QoQ % change calculation

Hello, everybody!

 

I think I need to calculate a very typical measure, and I get plenty of options on web, but nothing seems to work so far.

 

I need to calculate YoY, MoM, QoQ % changes of sales. So I have a column DATE with an exact date of a sales order and SALES with a corresponding amount of sales. What I want to get is a table with a hierarchy of dates and sales growth in %.

 

PREVIOUSYEAR does not seem to work because I have a hierarchy there and I put DATE field in the table. Many other options give me blank values. 

 

Please help =)

 

Thanks!

 

 

Need Help

3 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    9 years ago

    Hey,

     

    here you will find a pbix file

    This file contains a report page "MOM", here you will find a matrix visul that contains a measure (table: FactWithDates) called MOM Growth:

    MOM Growth = 
DIVIDE((CALCULATE(SUM(FactWithDates[Amount]))-CALCULATE(SUM(FactWithDates[Amount]),DATEADD('Calendar'[Date],-1,MONTH)))
,(CALCULATE(SUM(FactWithDates[Amount]),DATEADD('Calendar'[Date],-1,MONTH))),BLANK())*100

     

     

    Maybe this provides what you are looking for.

     

    Hope this helps

    • misul's avatar
      misul
      Icon for Helper I rankHelper I
      8 years ago

      Hello TomMartens , thanks for your answer, but the file you have linked doesn't exist anymore. I have a similar problem and wanted to check out the matrix visual.

       

      I have created a measure (Delta) to calculate YOY absolute value. But when I put it in a matrix, the measure is repeated. Did you have this issue in your matrix visual?

      Delta = calculate(sum(Table1[Sales]),Table1[Year]="2018")-calculate(sum(Table1[Sales]),Table1[Year]="2017") 

       

       

       

    • abhilash2612's avatar
      abhilash2612
      Regular Visitor
      5 years ago

      Hi,

       

      I did calculate the percentage increase by month (mom%), but the increased and decreseaed percentages are displayed in current column which should have been the next

       

      for example:

      jan 50

      feb 100

       

      got:

      jan      feb

      100%

       

      exepected

      jan             feb

      anyvalue   100%

       

      measure:

       

      Fare/Subsidy MoM% =
      IF(
          ISFILTERED('Query1'[TripTime]),
          ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
          VAR __PREV_MONTH =
              CALCULATE(
                  SUM('Query1'[Fare/Subsidy]),
                  DATEADD('Query1'[TripTime].[Date], -1, MONTH)
              )
          RETURN
              DIVIDE(SUM('Query1'[Fare/Subsidy]) - __PREV_MONTH, __PREV_MONTH)
      )
       
      Thanks
      Abhilash