How can I cumulate values for each day?

[danielakunz]

I have 2 columns, one containing serial numbers of machines and one containing a date like a time stamp, when the machine was edited. I want to sum up the count of machines each day.

eg:

Serial no. | Date 

S1 | 2/13/2020

S2 | 2/13/2020

S3 | 2/14/2020

S4 | 2/15/2020

S5 | 2/15/2020

-> 

Date | Count | cumulated values

2/13/2020 | 2 | 2

2/14/2020 | 1 | 3

2/15/2020 | 2 | 5

by using a quick measure it cumulates the values, but not by each day ascending but somehow randomly.

Thank you very much!

[amitchandak · Accepted solution]

You can do it with help from date table. Now if you need cumulative to reset on month, qtr or year. use date* or total* functions. ie datesmtd, totalmtd

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

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

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

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Tahreem24]

@danielakunz ,

YOu need to create two measure to accomplish you requirement:

Count_Measure = CALCULATE(COUNT(Table[SNO]),ALLEXCEPT(Table,Table[DATE]))

Cumultive_Measure = CALCULATE([Count],ALL(Table),Table[DATE]<=EARLIER(Table[DATE]))

See the screen shot of your expected result below:

Don't forget to give thumbs up 👍 and accept this as a solution if it helped you.

[Anonymous]

Please note that you need to have a date table in the model, and a link between the date column in the date table and the date column in the fact table. The Date[date] column mentioned is the one in the date table of course, and the Table[Date] table mentioned is the one in the fact table

[Anonymous]

CALCULATE(
                   COUNT(
                                 Table[Date],
                                 ),
                                FILTER(
                                           ALL(

                                                  Date
                                                 ),
                                                 Date[Date] <= MAX(Date[Date])
                                           )
)