Forum Discussion

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

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
11 months ago

Hi,

This may also be possible with visual calculations.  Share the download link of the PBI file and show the expected resilt there.

Dellis81's avatar
Dellis81
Icon for Post Prodigy rankPost Prodigy
11 months ago

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

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

      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

    • Dellis81's avatar
      Dellis81
      Icon for Post Prodigy rankPost Prodigy
      11 months ago

      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!