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 "Av...
Need Help
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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi v-ljerr-msft, This looks like a very smart idea, but I need help in actually calculating the Week measure and Month measure. How can I compare the Last week Average (from the selected timeframe) with the first week average? Same question for first month vs. last month. 

 

Many Thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi again v-ljerr-msft, 

     

    OK, I managed to create separate calculations for weekly trend and monthly trend, by calculating average of first week/month and last week/month

     

    Example (First Week Average): 

     

    Average First Week = CALCULATE (
    [Value],
    FILTER(ALLSELECTED('Calendar'),
    weeknum('Calendar'[Date],1)= WEEKNUM(min('Calendar'[Date]),1)
    ))

     

    I then calculated the trends as follows:

     

    Weekly Trend = ([Average Last Week]-[Average First Week])/[Average First Week]

     

    and finally, using your advice I created this measure: 

     

    Trend = IF( ISFILTERED ('Calendar'[Date]),[Daily Trend], IF (ISFILTERED ( 'Calendar'[Week] ),[Weekly Trend], IF(ISFILTERED( 'Calendar'[Month]),[Monthly Trend] ) ) )

     

    How do I now add "Trend" to the chart? It creates a line with zeros....

     

    Thanks!

     

     

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

      Hi Anonymous,

      How do I now add "Trend" to the chart? It creates a line with zeros....

      Oh, that could be a problem. The value of trend(percentage) is usually less then 1, but the Average Value is larger then 150. As they're shown on the same Y-Axis, the trend line will looks like zeros. :smileymad:

       

      To solve this issue, you can try using a combine Chart(Line and Column Chart), and show Average Value as Column Values and show Trend as Line Values, so that they will be shown on different Y-Axis. :smileyhappy:

       

      Regards

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago
        Hi v-ljerr-msft

        Yes, modifying the axis does help, but going back to what I wanted to achieve,
        I now have 3 good Trend results for days, weeks and Months. Each trend resultvus a single number representing the difference in percents between the average of 1st day/week/month to last day/week/month
        Placing a single result on a chart does not make sense, for example, if I calculate
        The difference between average of July May, the result on the graph will look like a moving average, right?

        If I want to stick to a kpi/trend, is ihere trend formula that I can add, that
        Will look like a real trend line?

        Using my phone for this post. If scteenshots are required I will add some early next week.

        Thanks a lot. And keep this smilies coming ☺👍