cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
asaft Regular Visitor
Regular Visitor

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

9 REPLIES 9
v-ljerr-msft Super Contributor
Super Contributor

Re: Calculated measure based on date hierarchy

Hi @asaft,

 

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

 

Regards

asaft Regular Visitor
Regular Visitor

Re: 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.pngDate 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.pngWeek 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.pngMonth View

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I hope this is now clearer. Thanks!

v-ljerr-msft Super Contributor
Super Contributor

Re: Calculated measure based on date hierarchy

Hi @asaft,

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. Smiley Happy

 

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. Smiley Happy

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

 

Regards

asaft Regular Visitor
Regular Visitor

Re: Calculated measure based on date hierarchy

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!

asaft Regular Visitor
Regular Visitor

Re: 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!

 

 

v-ljerr-msft Super Contributor
Super Contributor

Re: Calculated measure based on date hierarchy

Hi @asaft,


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. Smiley Mad

 

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. Smiley Happy

 

Regards

asaft Regular Visitor
Regular Visitor

Re: Calculated measure based on date hierarchy

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 ☺👍
asaft Regular Visitor
Regular Visitor

Re: Calculated measure based on date hierarchy

Hi @v-ljerr-msft

 

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

Monthly Trend.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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 Super Contributor
Super Contributor

Re: Calculated measure based on date hierarchy

Hi @asaft,


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. Smiley Happy

 

Regards