How can I use the Pay measure to output cumulative sum?
My attempted cumulative dax measure below outputs same Pay values. I’ve tried changing filter table context to ALL and ALLSELECTED but none make correct cumulative sums.
Pay Cumulative =
var _sumPay= SUMX(Hours_Worked, [Pay])
var _cumCalc =
CALCULATE(_sumPay,
FILTER(Hours_Worked, Hours_Worked[WeekEndingFriday] <= MAX(Hours_Worked[WeekEndingFriday])
)
)
return _sumPay
Screen shot below shows my failed cumulative measure same as Pay:
This is the case of “cart before the horse”. Your DAX looks fine to me, but is never going to produce accurate results, because you don’t have the foundation of a proper data model built beneath it.
In order to have any sort of time intelligence measures work properly, you will need to build out a star schema data model with a properly marked Date Table. I very strongly recommend using @Melissa 's awesome Extended Date Table.
If you have any questions about how to do this, I would highly recommend going through the solution video that @sam.mckay and I did last week for Power BI Accelerator Week #2 focused exactly on these sorts of data modeling issues:
The only reason the prior solution I developed worked was that it was basically a more complex version of the LOOKUPVALUE function, which is one of the few that does not require proper relationships between tables.
I’m confident that once you get the proper data model built, the cumulative total problem will basically solve itself.
If you have any questions after working through the video, please just give a shout.
