Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Calculated measure based on date hierarchy

Hi, 

 

I have a calendar with date/week/month hierarchy on which I show a line with a calculated "Average Value". 

 

I created another measure named "Trend" to calculate the ratio between the "Average Value" of the first selected date to the "Average Value" of the last selected date, as follows: 

 

Backlog Trend = (CALCULATE([Average Value]),LASTDATE('Calendar'[Date]))-CALCULATE([Average Value]]),FIRSTDATE('Calendar'[Date])))/CALCULATE([Average Value]),FIRSTDATE('Calendar'[Date]))

 

This calculation will always show the same result, no matter which date hierarchy is selected (day, week, month)

 

I would like to calculate this measure based on the selected hierarchy so that:

 

If a "Week" hierarchy is selected, the "Trend" measure will show the ratio between the average value of the first selected week to the Average Value of the last selected week

 

In the same manner, If a "Month" hierarchy is selected, the "Trend" measure will show the ratio between the average value of the first selected Month to the Average Value of the last selected Month

 

Is this possible? 

 

Thanks

Need Help

9 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous,

     

    What do you mean about "a "Week" hierarchy is selected"? Are you using the custom visual HierarchySlicer? Could you be more precisely with it by posting some screenshots? :smileyhappy:

     

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi v-ljerr-msft

       

      Sure, here are some screenshots that will explain this better. 

       

      First, this is the line chart with values of last 2 months, where the Date axis is set to days/dates:Date View

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      The last value on the line (which you cannot see) is 160, which is 9% less than the first value (175), therefore Trend=-9%

       

      Now I change the date hierarchy to show weeks in the shared axis:

      Week View

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      As you can see, the Trend result remains the same, as my "Trend" measure relates to the first and last values on the selected timeframe. However, it is now confusing. The values I show on the graph are averages. I would want now the measure to be able to compare the average value of the last week (162) with the average value of the first week (174). The result should be -7%. 

       

      So I am basically looking for a calculation that can be "aware" of the date hierarchy that I select in the graph. 

       

      A monthly view demonstrates the issue even better, as there is an increase in the monthly average value from 159 to 170, while "Trend" still shows -9%

      Month View

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      I hope this is now clearer. Thanks!

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi Anonymous,

        So I am basically looking for a calculation that can be "aware" of the date hierarchy that I select in the graph. 

        Thanks for the detailed explanation! Now I can understand it totally. :smileyhappy:

         

        Based on my experience, the calculation in a visual(Card visual in this case) cannot be "aware" of the date hierarchy that is selected in another visual(Line Chart in this case) currently. So an alternative way is to show the Trend measure in the same Chart with the Date Hierarchy, then use IF and ISFILTERED function to check which Hierarchy is selected, and use corresponding calculation to calculate the Trend. The formula below is for your reference. :smileyhappy:

        Trend =
IF (
    ISFILTERED ( 'Calendar'[Date] ),
    [Measure for Date],
    IF (
        ISFILTERED ( 'Calendar'[Week] ),
        [Measure for Week],
        IF ( ISFILTERED ( 'Calendar'[Month] ), [Measure for Month] )
    )
)

         

        Regards