Cumulative totals for all days except month end dates

[Abhilash_P]

Hello everyone,
I have date column and Sales amount column i wanted to created cumulative totals but below are the scenarios is should consider
If data is month end data the it should consider month end date sales insted of cumulative sales and next cumulation should start from that month end date below is the table 
Date and Sales Amount column are availbe in date nee output as requrired output column

Date	Sales Amount	Cumulative Sales	Required output	Calculation Logic
25 May 2023	5405994.94	5405994.94	5405994.94	25-May
26 May 2023	900540.11	6306535.05	6306535.05	25+26 May
27 May 2023	518406.73	6824941.78	6824941.78	25+26+27 May
28 May 2023		6824941.78	6824941.78	25+26+27+ 28 May
29 May 2023	891691.4	7716633.18	7716633.18	25+26+27+ 28+29 May
30 May 2023	9657511.64	17374144.82	17374144.82	25+26+27+ 28+29+30 May
31 May 2023	20182534.31	37556679.13	20182534.31	31 May 2023
01 June 2023	409500.45	37966179.58	20592034.76	31 May & 1 Jun
02 June 2023	7340905.18	45307084.76	27932939.94	31 May + 01 + 02 Jun
03 June 2023	306657.61	45613742.37	28239597.55	31 May + 01 + 02+03 Jun
04 June 2023		45613742.37	28239597.55	31 May + 01 + 02+03 + 04 Jun
05 June 2023	3462782.02	49076524.39	31702379.57	31 May + 01 + 02+03 + 04 + 05 Jun

[Anonymous · Accepted solution]

Hi @Abhilash_P ,

Please try to create a new column with below dax formula:

Column =
VAR cur_date = [Date]
VAR cur_sale = [Sales Amount]
VAR _a =
    EOMONTH ( [Date], 0 )
VAR _b =
    EOMONTH ( [Date], -1 )
VAR tmp =
    FILTER ( 'Table', [Date] <= EARLIER ( 'Table'[Date] ) && [Date] >= _b )
VAR _val =
    SUMX ( tmp, [Sales Amount] )
RETURN
    IF ( cur_date = _a, cur_sale, _val )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @Abhilash_P ,

Please try to create a new column with below dax formula:

Column =
VAR cur_date = [Date]
VAR cur_sale = [Sales Amount]
VAR _a =
    EOMONTH ( [Date], 0 )
VAR _b =
    EOMONTH ( [Date], -1 )
VAR tmp =
    FILTER ( 'Table', [Date] <= EARLIER ( 'Table'[Date] ) && [Date] >= _b )
VAR _val =
    SUMX ( tmp, [Sales Amount] )
RETURN
    IF ( cur_date = _a, cur_sale, _val )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Abhilash_P · Accepted solution]

Thanks @Anonymous