SumX? - cumulative running total

[Dellis81]

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!  

[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

[Dellis81]

Hello

I believe the existing measure currently in place is equivalent to your new suggestion.

   Any other thoughts?  Thank you!

=Var ReportValue = [End Animal Inventory Quantity]

return
ReportValue

[Dellis81 · Accepted solution]

@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

[v-prasare]

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 response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[Dellis81]

@Ashish_Mathur 

@lbendlin 

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=11...

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]

@Ashish_Mathur 

@lbendlin 

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_link

I do appreciate your help - and apologize for not being clear and concise in my communications.

thank you! 

[lbendlin]

Which one of these tabs contains the actual source data?  How is "Consolidated"  computed?

[Dellis81]

To answer your question (I think) - Excel PowerQuery transforms the two "dump" tabs together and computes the Consolidated Table which is then loaded into the excel data model. 

 DAX measures are then the "values" within Power Pivot table (tab Animal Day calc).   These power queries and Data Model loading should be accessable in the file.

[lbendlin]

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!

[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!

[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.

[Dellis81]

Hello!

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.

[Dellis81]

Hello Ashish!

Sorry for delay- back from a few days travel...

Just tried your recommendation - with result being the same as initial attempts.   Column G is your measure.   The yellow highlighted column is my desired goal.

I have no travel planned next two weeks, additional thoughts are greatly appreciated.

thank you!

[Dellis81]

thank you for your response.   Makes sense.

I am travelling this week, home tomorrow evening.    I will review first opportunitiy that i have quality focus time

Thank you, and will be in touch

[lbendlin]

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,..)