Forum Discussion
Calculated measure based on date hierarchy
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!
Microsoft EmployeeHi 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
- 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 ☺👍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!
- v-ljerr-msft
Hi Anonymous,
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?)
I think this could be done. Could you share a sample pbix file which can reproduce the issue? So that I can further assist on this issue. You can upload it to OneDrive or Dropbox and post the link here, or sent it to me in private message. Do mask sensitive data before uploading. :smileyhappy:
Regards