Forum Discussion

Dellis81's avatar
Dellis81
Icon for Post Prodigy rankPost Prodigy
10 months ago
Solved

SumX? - 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. ...
Need Help
  • Dellis81's avatar
    Dellis81
    10 months ago

    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

     

Praful_Potphode's avatar
Praful_Potphode
Icon for Super User rankSuper User
10 months ago

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's avatar
Dellis81
Icon for Post Prodigy rankPost Prodigy
10 months ago

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's avatar
    Praful_Potphode
    Icon for Super User rankSuper User
    10 months ago

    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's avatar
      Dellis81
      Icon for Post Prodigy rankPost Prodigy
      10 months ago

      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's avatar
        Dellis81
        Icon for Post Prodigy rankPost Prodigy
        10 months ago

        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