Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have 2 columns, one with values and the other with dates. The dates are non continous , for e.g. 10/10/2019, 12/10/2019, 20/10/2019 etc
I need to be able to get the difference between the sum of all the values from each date and previous date.
Is there a way to accomplish this?
I already have a column with the previous date gotten with this formula:
Predate = CALCULATE (
MAX (WeeklyOutstanding[Week] ), FILTER ( WeeklyOutstanding, WeeklyOutstanding[Week] < EARLIER ( WeeklyOutstanding[Week] ) ) )
Help would be greatly appreciated
Solved! Go to Solution.
Hi @Anonymous,
You can try measure like the following
Delta From Previous =
var prev_date = CALCULATE(MAX('WeeklyOutstanding'[Week]), FILTER(all('WeeklyOutstanding'), 'WeeklyOutstanding'[Week] < SELECTEDVALUE('WeeklyOutstanding'[Week])))
return
sum('WeeklyOutstanding'[amount]) - CALCULATE(SUM('WeeklyOutstanding'[amount]), FILTER(ALL('WeeklyOutstanding'), 'WeeklyOutstanding'[Week] = prev_date))
Hope that Helps
Richard
Proud to be a Super User!
This worked great! Thank you!
The only issue now is that somehow it only works with the full data, whenever I use filters its gives the wrong amounts. Do you know why?
It is removing the filter context with
all('WeeklyOutstanding'[Week])
, you may need to change that be just
ALL('WeeklyOutstanding')
Proud to be a Super User!
Hi @Anonymous,
You can try measure like the following
Delta From Previous =
var prev_date = CALCULATE(MAX('WeeklyOutstanding'[Week]), FILTER(all('WeeklyOutstanding'), 'WeeklyOutstanding'[Week] < SELECTEDVALUE('WeeklyOutstanding'[Week])))
return
sum('WeeklyOutstanding'[amount]) - CALCULATE(SUM('WeeklyOutstanding'[amount]), FILTER(ALL('WeeklyOutstanding'), 'WeeklyOutstanding'[Week] = prev_date))
Hope that Helps
Richard
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |