Forum Discussion
Post ProdigySumX? - cumulative running total
Hello
I am in need of help with a running total calculation. I am wanting to derive formula that calculates cumulative animal days based on animal inventory over a specified calendar interval.
The Animal day calculation uses a "Begin Inventory" measure for each group, and over a daily calendar table. Column F (Red) is the result of both measures below with only difference is the table table iterator. Column G is the desired goal.
I believe SumX is what I need to be using, and have studied numerous forum questions/videos - but something is obviously wrong. Thank you for your responses.
=sumx(values('Calendar'[Dates]),calculate([Begin Animal Inventory Quantity],filter(values('Calendar'),'Calendar'[Dates]<=[MaxDate])))=sumx(values(GroupSetup[Group ID]),calculate([Begin Animal Inventory Quantity],filter(values('Calendar'),'Calendar'[Dates]<=[MaxDate])))I have also attempted to use the "ALL" vs "Values" with varying degrees of wacky/unexplained results.
Note, i have a very simple data model, rows within pivot table are pulling from the two dimension tables (left).
Thank you!
lbendlin Ashish_Mathur v-prasare
Guess what - think I got the problematic measure to work :). I remain confused in many areas of DAX - but I can move forward now. Thank you to all three of you for your suggestions.
My solution was to place Animal Days in a SumX iterator - and that resulted in the group total work, as well as the cumulative animal day measure. Something simple, but yet so confusing.
=Var ReportValue = SUMX(VALUES('Calendar'[Dates]),[End Animal Inventory Quantity]) return ReportValue
20 Replies
- lbendlin
Super User
First port of call should be the Quick Measure for the Running Total scenario. Have you given that a try?
Once you have seen that code you can "improve" it by using WINDOW(1,ABS,0,REL,..)
- Ashish_Mathur
Hi,
This may also be possible with visual calculations. Share the download link of the PBI file and show the expected resilt there.
Dellis81Hello!
Unfortunately -
1) I am needing a measure, as the animal day calculation will be used in a multitude of other measures.
2) I only have this datamodel in excel right now - eventual goal is to move to PBI.
Here is a google drive link.
https://drive.google.com/drive/folders/1-dKYRdtfqyVYNsHLCZrLO_v08gaUFruI?usp=sharing
Please look at the AnimalDay tab, the cells in yellow are the desire outcome.
I do THANK YOU in advance!!
daryl
- Ashish_Mathur
Hi,
This DAX measure pattern should work
Measure = calculate([animal days],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))
Hope this helps.
v-prasareCommunity Support
Hi Dellis81,
We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.
Ashish_Mathur & lbendlin ,Thanks for your prompt responseThank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support- Dellis81
Good Morning!
Back from my travel. I just responded to Ashish with his most recent thoughts.
Appreciate your responses - I should be more available next 2 weeks.
thank you!
- Dellis81
Hello - worked on a little more today. Added a second set of measures - Feed Fed & Cumulative feed fed - these measures worked as expected.
I am using the same DAX pattern for Cumulative Animal Days and Cumulative Feed Fed. I suspect my "Animal Day" measure is slightly more complex. My thought process - A single Day of End Invty would equal would equal animal days for that day. With the cumulative summing up Animal days across prior calendar days.
=SUMX ( VALUES ( 'Consolidated'[Group ID] ), CALCULATE ( [Animal Days], FILTER ( ALL ( 'Calendar' ), 'Calendar'[Dates] <= MAX ( 'Calendar'[Dates] ) ) ) )
The Ending Inventory is basically derived from purchases - Deaths - sales cumulative change.=Var EndInvty = calculate([Animal Inventory Change],FILTER( ALL( 'Calendar'), 'Calendar'[Dates] <= max( 'Calendar'[Dates] ))) return if (and(EndInvty=0,(isblank([Animal Inventory Change]))),BLANK(),EndInvty)Link to revised file
https://docs.google.com/spreadsheets/d/1uTodTRAgZDNXXMiryts7tXh73WN637Wg/edit?usp=drive_link&ouid=110148896366346469715&rtpof=true&sd=trueThank you!
- lbendlin
Not clear which sheet contains your source data and which is the expected result. Can you please clarify.
- Praful_Potphode
Hi Dellis81
try below measures:
Animal Days =[Begin Inventory]Cumulative Animal Days = CALCULATE( [Animal Days], FILTER( ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) ) )Cumulative Animal Days by Group = CALCULATE( [Animal Days], FILTER( ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) && 'Consolidated'[Group ID] = MAX('Consolidated'[Group ID]) ) )Cumulative Inventory := CALCULATE( SUM('Consolidated'[Begin Inventory]), FILTER( ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) ) )Cumulative Metric by Group := CALCULATE( SUM('Consolidated'[YourMetricColumn]), FILTER( ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) && 'Consolidated'[Group ID] = MAX('Consolidated'[Group ID]) ) )Please give kudos or mark it as solution once confirmed.
Thanks and Regards,
Praful
- Dellis81
Praful - thanks for the ideas. But as I was attempting to incorporate - the 1st obstacle was your measure "Animal Days". As you know - the sum function only accepts table arguments. The "Begin Inventory" reference is a measure name - thus unable to incorporate into a Sum function? OR am I missing something?
Appreciate your thoughts - anything else you might suggest?
Animal Days := SUM('Consolidated'[Begin Inventory])- Praful_Potphode
Hi Dellis81 ,
i have modified the first measure in my solution.
please give it a try and let me know.
Thanks and Regards,
Praful
