cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Cumulative sum for the year for each month

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 :

1 ACCEPTED SOLUTION
Super User

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
3 REPLIES 3
Regular Visitor

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

Super User

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Regular Visitor
``````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)))))
``````

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.