Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Cumulative Sum by month

Greetings everyone,

 

I'm having trouble displaying the cumulative sum of the Launch Number from my database.

 

database 

 

Basically, I have a slicer with the months of the year and I would like that when I select a month (ex: April), I receive the sum of Jan, Feb, Mar and Apr.

 

In the example, if I select April, I would like to have the value 4 (Goal only).

 

I am using the following formulas:

 

Launch Number = CALCULATE(SUM(MetricsTAB[Value]);MetricsTAB[Metric]="Launch Number")
Launch Number Goal YTD = CALCULATE([Launch Number];FILTER(ALL(MetricsTAB);MetricsTAB[Month Date] <= TODAY());MetricsTAB[A/G/P]="Goal")

 

However, it is not the best solution, because if you select February, the value remains 4, instead of 0.

 

Any thoughts?

 

Best regards,

Need Help
Show and Tell
Tips and Tricks

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , The information you have provided is not making the problem clear to me. Can you please explain with an example.

    You can explore YTD

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((Table[Date]),"12/31"))

    cumulative

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=maxx(date,date[date])))
    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(Sales[Sales Date])))

     

    Or rolling

    Rolling 4 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table[Date],ENDOFMONTH(Sales[Sales Date]),-4,MONTH))
    Rolling 4 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table[Date Filer],MAX(Sales[Sales Date]),-4,MONTH))

    Appreciate your Kudos.