Forum Discussion
Responsive ResidentUsing delayed costs in a cumulative total
hello all!
I have created a measure wih a parameter, so users can delay a percentage of costs to next year ([Jaar]).
UP - Zonder advies (vertr) =
var __current =
// calculate planned cost for current year.
CALCULATE(
[UP - Zonder advies] * ( 1 - par_delay[par_delay Value]),
FILTER(
ALL('Datumtabel'),
Datumtabel[Jaar] = MAX(Datumtabel[Jaar]))
)
var __delayed =
// calculate costs that a delayed for a year.
CALCULATE(
[UP - Zonder advies] * par_delay[par_delay Value],
FILTER(
ALL('Datumtabel'),
Datumtabel[Jaar] = MAX(Datumtabel[Jaar]) - 1 )
)
RETURN
// sum of current and delayed
__current + __delayed
Individualy this measure does exactly what i want. Now I want to use this measure in a cumulative measure. So they can see the effect of the delay in a prognoses
UP - Zonder advies (cumulative) =
CALCULATE(
[UP - Zonder advies (vertr)],
Datumtabel[Date] <= MAX(Datumtabel[Date])
)
UP - Zonder advies (cumulative) however returns the same values as UP - Zonder advies (vertr). I Think it has something to do with the filtercontext of one measure cancelling out the context needed for the other. Howeven I can't seem to figure it out.
Hopefully you guys can help me out!
Hopefully you guys can help me out 🙂
Anonymous Kedar_Pande Bibiano_Geraldo ,
Thank you all for your replies! I have solved my problem
Turned out the filter context from the measures did indeed interfere in the cumulative total. I solved it by first splitting the '__current' and '__delayed' variables into seperate measures. for the __current I could use a normal cumulative. For the __delayed measure I used the following cumulative total to get the delay in.UP - Zonder advies - vertraagd (**bleep**) = CALCULATE( [UP - Zonder advies] * par_kosten_vertraging[% kosten vertraging Value], FILTER( ALL(Datumtabel), (Datumtabel[Date]) <= MAX(Datumtabel[Date]) - 1 ) )
7 Replies
- Kedar_Pande
Super User
UP - Zonder advies (cumulative) =
CALCULATE(
[UP - Zonder advies (vertr)],
FILTER(
ALL(Datumtabel[Date]),
Datumtabel[Date] <= MAX(Datumtabel[Date])
)
)💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn- Chewdata
Kedar_Pande thanks for your reply. Unfortunalely it returns the same values (not cumulative)
- Bibiano_Geraldo
Hi Chewdata ,
Please use this DAX to get comulative:
UP - Zonder advies (cumulative) = VAR CurrentDate = MAX(Datumtabel[Date]) RETURN CALCULATE( [UP - Zonder advies (vertr)], FILTER( ALL(Datumtabel), Datumtabel[Date] <= CurrentDate ) )- Chewdata
Bibiano_Geraldo thanks for your reply. Unfortunalely this also does not seem to fix the problem.
- Bibiano_Geraldo
can you share a sample file with no sensitive data, and desired output?
Thank you Bibiano_Geraldo and Kedar_Pande
Hi, Chewdata
Based on your description, I've created the following sample data:
I use the following DAX expression to get the cumulative value:
UP - Zonder advies (cumulative) = SUMX(FILTER(ALLSELECTED(Datumtabel),'Datumtabel'[Date]<=SELECTEDVALUE(Datumtabel[Date])),[UP - Zonder advies (vertr)])I've provided the PBIX file used this time below. If you are not familiar with this aspect, you can read some articles that will help you:
Computing running totals in DAX - SQLBI
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Chewdata
Anonymous Kedar_Pande Bibiano_Geraldo ,
Thank you all for your replies! I have solved my problem
Turned out the filter context from the measures did indeed interfere in the cumulative total. I solved it by first splitting the '__current' and '__delayed' variables into seperate measures. for the __current I could use a normal cumulative. For the __delayed measure I used the following cumulative total to get the delay in.UP - Zonder advies - vertraagd (**bleep**) = CALCULATE( [UP - Zonder advies] * par_kosten_vertraging[% kosten vertraging Value], FILTER( ALL(Datumtabel), (Datumtabel[Date]) <= MAX(Datumtabel[Date]) - 1 ) )
