Forum Discussion

KaroRoza's avatar
KaroRoza
Helper I
6 months ago
Solved

cummulative sum not working

Hi,

Could someone help me on Cummulative sum? I have no idea why it is not working.

I have two tables: Actuals and DimDate (relation one to many)

In the dashboard I have selector DimDate( Year, Month).

My measure:

Cumm SUM USD = CALCULATE(sum('Actuals'[Actual  USD]), filter(ALL(Actuals[Date]), Actuals[Date]<= today()))

 

I am not getting cummulative sum month by month. 

Y axis - is Date from Actual table.

X axis - Cumm SUM USD - I am getting values month by month... not sum up to that month


How should I modify it to get expected results?
Please help

Need Help
  • johnt75's avatar
    johnt75
    6 months ago

    That's actually simpler DAX code, you can use the DATESYTD function

    Cumm Sum USD =
CALCULATE ( SUM ( 'Actuals'[Actual USD] ), DATESYTD ( 'DimDate'[Date] ) )

6 Replies

  • wardy912's avatar
    wardy912
    Super User
    6 months ago

    Hi KaroRoza 

     

     Please give the following a try:

     

    Cumm SUM USD =
CALCULATE(
    SUM(Actuals[Actual USD]),
    FILTER(
        ALLSELECTED('DimDate'[Date]),
        'DimDate'[Date] <= MAX('DimDate'[Date])
    )
)

     

    The FILTER iterates from the earliest visible date up to the current row date.

     

    --------------------------------

    I hope this helps, please give kudos and mark as solved if it does!

     

    Connect with me on LinkedIn.

    Subscribe to my YouTube channel for Fabric/Power Platform related content!

     

    • v-tejrama's avatar
      v-tejrama
      Community Support
      6 months ago

      Hi KaroRoza ,

      Thank you wardy912  for the response provided!

      Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

      Thank you.

       

  • johnt75's avatar
    johnt75
    Super User
    6 months ago

    There's a couple of points here. Its best practice to use fields from your date table in visualisations and measures,  rather than using the date field from your fact table.

    Also, you are filtering all the dates before today, rather than filtering for all the dates before the last date visible in the context.

    Try

    Cumm Sum USD =
VAR MaxDate =
    MAX ( 'DimDate'[Date] )
VAR Result =
    CALCULATE ( SUM ( 'Actuals'[Actual USD] ), 'DimDate'[Date] <= MaxDate )
RETURN
    Result
    • KaroRoza's avatar
      KaroRoza
      Helper I
      6 months ago

      johnt75 
      Thank you, it partially works ( sums per month are correct - cummulative).
      I would like to have a possibility, that the sum will be only fro selected Year/Month in Slicer.
      I am having sales for 3 years : 2024-2026, 
      then if I select 2025 in Selector, then graph should adjust to only 2025 months, but values are still counted from the beggining ( since 2024).
      Could you please help me on that as well?

      Very much approciated.

      • johnt75's avatar
        johnt75
        Super User
        6 months ago

        That's actually simpler DAX code, you can use the DATESYTD function

        Cumm Sum USD =
CALCULATE ( SUM ( 'Actuals'[Actual USD] ), DATESYTD ( 'DimDate'[Date] ) )