Forum Discussion

Atanas_Atanasov's avatar
Atanas_Atanasov
Helper II
4 years ago

Cumulative Total Not Adding Up New Values

Dear All, 

I'd like to sum up all hours from the screenshot up to 31.Oct.2021 268,088.83, then add up to them only the new hours for Nov-2021 22,907.57 and substract the Sold_Hours_Completed only for Nov-2021 8,983.89 and to get to get Total Sold Hours Test of 282,012,51 hours. Then to add to them the Hours_New for Dec-2021 13,700.38 and substract again the Sold_Hours_Completed for Dec-2021 20,636.18 and to get Total Sold Hours Test for Dec-2021 of 275,076.71.

Any help will be deeply appreciated. 
Thank you in advance, 
Atanas

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Atanas_Atanasov , Can you share formula, Using a truncated date

     

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

     

    or

     

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

     


    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Atanas_Atanasov's avatar
      Atanas_Atanasov
      Helper II
      4 years ago

      amitchandak ,
      Thank you for your swift reply.
      Here's the formula I used for the cumulative total:

      Cumulative Test =
      IF(
      MIN(
      'Calendar'[Date]
      ) >= DATE(2021,10,31),
      BLANK(),
      CALCULATE(
      [Hours_Base],
      FILTER(
      ALL(
      'Calendar'[Date]
      ),
      'Calendar'[Date] <= MAX('Calendar'[Date])
      )
      ))

      Also, here is a link with the sample data with the dates and the numbers. Please let me know if you need something more from my end, and thank you one more time, 
      Atanas