topic Re: Cumulative sum for the year for each month in DAX Commands and Tips

Cumulative sum for the year for each month

Safus09 — Tue, 02 Apr 2024 21:03:32 GMT

Hi, I'am new to power BI and Dax queries and I want to calculate a cumulative sum for each month starting from the 01/01 of each year.

Example : For period1 of 2023 => Sum starting from 01/01/2023 until the 31/01/2023
For period2 of 2023 => Sum starting from 01/01/2023 until the 28/02/2023 (So Sum of period1 + Period 2)

etc ...

For period12 of 2023 => Sum starting from 01/01/2023 until the 31/12/2023 (So Sum of period1 + Period 2 + Perdiod3 + ... + Period12 )

I tried the following :

DebitExercice = CALCULATE(sum('General ledger attributes'[PRIMARYAMOUNT]), 'General ledger attributes'[ISCREDIT] = "No", FILTER( ALLSELECTED('Fiscal periods'[Date],'Fiscal periods'[Fiscal year],'Fiscal periods'[Period Month]),'Fiscal periods'[Date] >= DATE('Fiscal periods'[Fiscal year],01,01) && 'Fiscal periods'[Date] <= date('Fiscal periods'[Fiscal year],'Fiscal periods'[Period Month],DAY(EOMONTH('Fiscal periods'[Date],0)))))<br /><br /><li-code lang="markup">DebitExercice = CALCULATE(sum('General ledger attributes'[PRIMARYAMOUNT]), 'General ledger attributes'[ISCREDIT] = "No", FILTER( ALLSELECTED('Fiscal periods'[Date],'Fiscal periods'[Fiscal year],'Fiscal periods'[Period Month]),'Fiscal periods'[Date] >= DATE('Fiscal periods'[Fiscal year],01,01) && 'Fiscal periods'[Date] <= date('Fiscal periods'[Fiscal year],'Fiscal periods'[Period Month],DAY(EOMONTH('Fiscal periods'[Date],0)))))

What I'am missing here ?
The result :

Re: Cumulative sum for the year for each month

Safus09 — Tue, 02 Apr 2024 21:06:17 GMT

Sorry I don't know how to modify a post, so I repost the corrected used code.

Re: Cumulative sum for the year for each month

danextian — Tue, 02 Apr 2024 23:14:10 GMT

Hi Safus09 ,

Assuming you have separate dates table with FY info, try the following

Total Transactions YTD FY2 = CALCULATE ( [Total Transactions], DATESYTD ( Dates[Date], "Jun 30" ) )

Transactions YTD FY = CALCULATE ( [Total Transactions], FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) && Dates[FY] = MAX ( Dates[FY] ) ) )

Please see attached pbix for details.

Re: Cumulative sum for the year for each month

Safus09 — Wed, 03 Apr 2024 13:52:22 GMT

danextian , Thank you ! The DATESYTD function did the job.