add running Total/cumulative sum Column to powerbi dataset

[Ajaal]

Hi There...

 I need to add running Total or cumulative sum Column to Sales column in my powerbi dataset which looks like

Product	Date	Sales
A	01/01/2022	1130
B	02/01/2022	2000
C	03/01/2022	2100
A	01/02/2022	1500
B	02/02/2022	1915
C	03/02/2022	2599
A	01/03/2022	1750
B	02/03/2022	2800
C	03/03/2022	3099
A	01/04/2022	2200
B	02/04/2022	2900
C	03/04/2022	3300

and It should look like ...

Product	Date	Sales	Cum_Sale
A	01/01/2022	1130	1130
B	02/01/2022	2000	2000
C	03/01/2022	2100	2100
A	01/02/2022	1500	2630
B	02/02/2022	1915	3915
C	03/02/2022	2599	4699
A	01/03/2022	1750	4380
B	02/03/2022	2800	6715
C	03/03/2022	3099	7798
A	01/04/2022	2200	6580
B	02/04/2022	2900	9615
C	03/04/2022	3300	11098

how DAX function should be added to get the above column "Cum_Sales"..?

Thnx Much...

[Ashish_Mathur]

Hi,

If you want a measure solution, then try this

1. Create a Calendar Table
2. Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table
3. To your visual, drag Product and Date (from the Calendar Table)
4. Write these measures

Total = sum(Data[Sales])

Running total = if([total]=blank(),blank(),calculate([Total],datesbetween(calendar[date],minx(all(calendar[date]),calendar[date]),max(calendar[date]))))

Hope this helps.

[danextian · Accepted solution]

Hi @Ajaal 

Please try this:

Cum_Sales =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        FILTER ( 'Table', 'Table'[Product] = EARLIER ( 'Table'[Product] ) ),
        'Table'[Date] <= EARLIER ( 'Table'[Date] )
    )
)