Forum Discussion

W4LTZ's avatar
W4LTZ
Frequent Visitor
2 years ago

Percentage difference between periods that change with date granularity on graph

Hi All,

 

Spent my whole morning on this one and thought i'd come here for help before i lose all my hair 🙂

I've created the below graph which includes the measure '% Change - Month'  calculating the difference in 'Ave. Life' from the previous month in the bar columns.

The measure is calculated via this formula.

IF(
    ISFILTERED('Scrapped'[Scrap Date]),
    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(
            AVERAGE('Scrapped'[Hours]),
            DATEADD('Scrapped'[Scrap Date].[Date], -1, MONTH)
        )
    RETURN
        DIVIDE(AVERAGE('Scrapped'[Total Hours]) - __PREV_MONTH, __PREV_MONTH)
)

 

What i can't figure out is how to adjust the date range calculations so the measure '% Change - Month' follows the date granularity shown on the x-axis.

 

For example if i drill up to quarters the calculation is obviously wrong as the formlua is based on the previous month.

 

So how do i change this so the % Change can be dynamic based on quarter, year etc?

 

Thank you so much!

 

Need Help
Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi W4LTZ ,

     

    Modify your formula like below:

    % Change - Dynamic =
IF (
    ISFILTERED ( 'Scrapped'[Scrap Date] ),
    ERROR ( "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column." ),
    VAR __PREV_PERIOD =
        CALCULATE (
            AVERAGE ( 'Scrapped'[Hours] ),
            DATESINPERIOD (
                'Scrapped'[Scrap Date].[Date],
                MIN ( 'Scrapped'[Scrap Date].[Date] ),
                -1,
                MONTH
            )
        )
    RETURN
        DIVIDE ( AVERAGE ( 'Scrapped'[Total Hours] ) - __PREV_PERIOD, __PREV_PERIOD )
)

     

    If it does not work please provide test data and explain.

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • W4LTZ's avatar
      W4LTZ
      Frequent Visitor
      2 years ago

      Hi Anonymous / Adam,

       

      Thank you so much for your attention.

       

      I have tried your formula suggestion and it doesn't seem to work for neither month or quarters.

       

      I have detailed the information in the test file below.

      https://www.dropbox.com/scl/fi/3e09g37687v5h6qh3jrwp/Test.pbix?rlkey=0vwtmrvgtc78oh8lrpy9pu06w&dl=0

       

      The visuals now look like the below. Basically i need the % change from the previous period to be consistent with the date hierachy shown.

       

      Hope this helps and that the dropbox link works. Please feel free to ask any questions.

       

      Cheers

       

      Walt