Forum Discussion
Helper IIDax Filter - Comparing sum on specific date against month start
Dear team,
need som help from you geniusses agian as I can't seem to figure this out for the life of me:
I have a simple table as shown below (except yellow - it is what im trying to achieve):
The table displays several date entries and captures the remainder of "Open Orders" per that date alongside the "sales" from that very same date. Both added together result in what I project to be the outcome by the month's end per that according date.
Now what I'm looking for is the change in % vs. the starting value of the month. So Projected value from 02.11.2022 vs. 01.11.2022, or projected 04.11.2022 vs. 01.11.2022, etc. etc.
What I've tried was coding it in Dax as such:
Delta month start =
sum(Projected) /
calculate( sum (Projected), FIRSTDATE(Date_Archived)) ) - 1
It doesnt seem to be running my way tho.
Could anyone kindly point me in the right direction here, please?
Thanks so much in advance!
Best,
Alex
Hi awolf88 ,
Please try:
Measure = VAR _a = CALCULATE ( SUM ( 'Table'[Projected end of month] ), FILTER ( ALL ( 'Table' ), [Date_Achieved] = MINX ( ALL ( 'Table'[Date_Achieved] ), [Date_Achieved] ) ) ) VAR _b = SUM ( 'Table'[Projected end of month] ) RETURN DIVIDE ( _b - _a, _a )Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
v-jianboli-msftCommunity Support
Hi awolf88 ,
Please try:
Measure = VAR _a = CALCULATE ( SUM ( 'Table'[Projected end of month] ), FILTER ( ALL ( 'Table' ), [Date_Achieved] = MINX ( ALL ( 'Table'[Date_Achieved] ), [Date_Achieved] ) ) ) VAR _b = SUM ( 'Table'[Projected end of month] ) RETURN DIVIDE ( _b - _a, _a )Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
awolf88Thank you so much for your help, Jianbo! Much appreciated!