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
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 ☺👍
      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi v-ljerr-msft

         

        Here's a screenshot that will probably help us conclude this thread (-;

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        As you can see, the "Monthly Trend" measure returns the correct result ((28-27)/27)=4%

         

        However, when using the conditional "Trend" measure as a line, it does some calculations on every month, like a moving average or something like that. 

         

        Is it possible to make the measure result (In this case 4%) displayed like a dotted average line with the same value along the whole chart? (As if Y=0.04 all the way?)

         

        Thanks!