YoY / MoM / QoQ % change calculation

[Katerina_e]

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!

[TomMartens]

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

[abhilash2612]

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

[misul]

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")