Forum Discussion

Blue-Space_34's avatar
Blue-Space_34
Frequent Visitor
2 years ago

Cumulative sum by year-month

Hello,

 

I have this table that has a year-month column and a count by month. Please, note that the count refers to that particular year-month combination and sometimes you have more rows with the same year-month combination (and count) due to multiple dates:

 

 

The column called "Cumulative monthly value" is the desired output:

it is just a sum of the count of that particular month + cumulative count of the previous available year-months.

For example: cumulative monthly value in 1996-Jun = 7 (= 3+4)

 

I would like to write a DAX expression to get a calculated column like "cumulative monthly value" that calculates the cumulative value adding that year-month count to the cumulative previous value.

Alternatively it would be great to have an DAX expression that does the same with a measure.

In the end I would like to get this final result in a visual:

 

 

Thank you so much for your time and help

Need Help
Tips and Tricks

5 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

    Hi Blue-Space_34 ,

     

    You mentioned that you have multiple dates, I'd use the dates instead of the year month. Start by creating a separate dates table if you don't have one yet. Relate that to you fact table and create this measure.

    Cumulative Measure = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
)

    Now that is  a measure. It would be a different approach with calculated columns. The dates can repeat and so you'll get repeating cumulative values and more so if you use month-year (this will be sorted alphabetically so this must be custom sorted) and they cannot be sliced dynamically according to the other columns in your table/s unless explict in the calcualtion.  If you follow this route, create an index column in M and run a cumulative calcuation against that index column and the date. Below are cumulative calc columns based on date, date + index and month-year.

    Please see attached pbix for details.

     

     

     

    • Blue-Space_34's avatar
      Blue-Space_34
      Frequent Visitor
      2 years ago

      Thank you for your reply danextian ,

       

      In this case, I'm not sure if using the dates apply: I have a dates column but dates are not unique in the table, so some dates are repeated. 

      The only count available is by month and the cumulative must be done aggregating the count month after month.

      Therefore is it possible to use the count by year-month in the measure approach instead?

       

      If not, the calculated column approach seems a practical one, I created an index column.

      Please, could you please provide more details on how to achieve the cumulative column, cumulative column index and cumulative column (year month) in the second image you provided?

       

      Thanks again in advance for your answer!

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    You will want to invest in an index column - something that can make your rows unique and sortable.

     

    Then you can look into the Quick Measures - one of them covers Running Totals.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number.  Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.  To your visual, drag Year and Month name from the Calendar Table.  Write these measures:

    S = sum(Data[count])

    Running sum = calculate([S],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

    Hope this helps.