cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

[DAX] Measure which returns the difference (%) between daily totals and a baseline value?

DateValue
01/01/2020500
... 
29/02/2020650
01/03/2020700
02/03/2020600
... 
31/08/2020550

 

I am calculating a baseline value as the median of January and February values (bolded in table above):

 

Baseline Value = CALCULATE(MEDIAN('Table'[Value]), 'Table'[Month Number] < 3)
 
I want to determine the % difference between Baseline Value and all future values (i.e. months >= 3). I imagine the pseudo-code to look something like:
 
Difference From Baseline = DIVIDE('Table'[Value] - 'Table'[Baseline Value], 'Table'[Baseline Value])

 

When I attempt to plot Difference From Baseline on a line chart, I get a flat line for all future dates. I am guessing I need to somehow FILTER for each unique date? Any help is much appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver II
Resolver II

@jackCoD  Please change the baseline formula as follows then it should work

 

Baseline Value = CALCULATE(MEDIAN('Table'[Value]), MONTH('Table'[Date]) < 3)

Difference From Baseline = DIVIDE(SUM('Table'[Value])-[Baseline Value],[Baseline Value])

 

Example output:

 

bi.png

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

Cheers!

View solution in original post

2 REPLIES 2
Highlighted
Resolver II
Resolver II

@jackCoD  Please change the baseline formula as follows then it should work

 

Baseline Value = CALCULATE(MEDIAN('Table'[Value]), MONTH('Table'[Date]) < 3)

Difference From Baseline = DIVIDE(SUM('Table'[Value])-[Baseline Value],[Baseline Value])

 

Example output:

 

bi.png

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

Cheers!

View solution in original post

Highlighted

Thanks. The problem was my Baseline calculation as you suggested.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors