Forum Discussion
Post ProdigySumX? - cumulative running total
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
Community SupportHi 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 response
Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support
Dellis81Good 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
Super User
Not clear which sheet contains your source data and which is the expected result. Can you please clarify.
- Dellis81
I'm sorry!
Sheet 6(2) is my pivot table summary with Dax measures. The remaining tabs are either source data or misc tabs in my process.
I think you had mentioned using the windows function. Are those available in Excel? I am needing to keep inside excel for additional analysis and printing functions.Thank YOU!
thank you!
- Ashish_Mathur
Too may tabs and too many measures creating more confusion than clarity. Share a much smaller dataset with only the information which is necessary. Show the expected result and calculation logic there.
- Dellis81
Hello, I've attempted to cleanup/simplify the spreadsheet to only relevant measures. I have moved all the required measures to a _measures table. The hilighted measures are related to the problematice Animal Day calculation.
Below is the excel pivot table I am working with. Column I (in green) is my desired result for Column F (cumulative animal days). The measures relating to "Feed Fed" are working as expected - with equivalent DAX pattern for Animal Days Cumulative.
And just to be safe - including a new link.
https://drive.google.com/drive/folders/1-dKYRdtfqyVYNsHLCZrLO_v08gaUFruI?usp=drive_linkI do appreciate your help - and apologize for not being clear and concise in my communications.
thank you!
