Forum Discussion
Calculated measure based on date hierarchy
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!
Microsoft EmployeeHi 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
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!
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
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