topic Re: Current year YTD and Last year YTD DAX Formula in DAX Commands and Tips

Current year YTD and Last year YTD DAX Formula

Bertje123456 — Mon, 02 Dec 2024 14:23:50 GMT

Hi,

I have question about YTD Current year and YTD Last year DAX formula. Suppose I have data like below.

I want to make a clustered column chart where the current year YTD only shows the fully closed months, so for the year 2024, it should display the months from January until November (because we are now at the beginning of December). And where the last year YTD shows everything for the entire year 2023 (the months from January until December).

CY YTD =

CALCULATE(

SUM('TableF'[TotalSales]),

'TableD'[Year] = YEAR(TODAY()),

'TableD'[Month number] < MONTH(TODAY())

)

LY YTD =

CALCULATE(

SUM('TableF'[TotalSales]),

'TableD'[Year] = YEAR(TODAY()) - 1

)

I am having trouble creating a DAX formula for last year YTD. Can someone help me with this DAX formula?

If the DAX formula for current year YTD is incorrect, I would like to hear what the improvements are.

Thanks

Re: Current year YTD and Last year YTD DAX Formula

xifeng_L — Mon, 02 Dec 2024 14:56:06 GMT

Hi Bertje123456 ,

You can try below measure.

CY YTD = CALCULATE( SUM('Fact'[value]), KEEPFILTERS('Calendar'[Date]<=EOMONTH(TODAY(),-1)), 'Calendar'[Year]=YEAR(TODAY()) )

LY YTD = CALCULATE( SUM('Fact'[value]), KEEPFILTERS('Calendar'[Date]<=EOMONTH(TODAY(),-1)), 'Calendar'[Year]=YEAR(TODAY())-1 )

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: Current year YTD and Last year YTD DAX Formula

DataNinja777 — Mon, 02 Dec 2024 14:57:56 GMT

Hi Bertje123456 ,

To calculate YTD for the current year and the previous year in Power BI, the best practice is to use a properly configured Calendar table and DAX time intelligence functions. The Calendar table should include a continuous date range and attributes such as Date, Year, and Month, and it should be linked to your fact table on the Date column.

For the current year YTD, you can calculate only fully closed months by using the DATESYTD function combined with a filter to exclude the current (incomplete) month. The formula is as follows:

CY YTD = CALCULATE( SUM('TableF'[TotalSales]), DATESYTD('Calendar'[Date]), FILTER('Calendar', 'Calendar'[Date] < EOMONTH(TODAY(), -1)) )

This ensures that only dates up to the last day of the previous month for the current year are included.

For the last year YTD, you can include all months by using the SAMEPERIODLASTYEAR function with DATESYTD. The formula is as follows:

LY YTD = CALCULATE( SUM('TableF'[TotalSales]), SAMEPERIODLASTYEAR(DATESYTD('Calendar'[Date])) )

This approach dynamically shifts the context to the previous year and calculates the year-to-date total for all months.

Using these formulas ensures scalability, accuracy, and alignment with DAX best practices by leveraging the Calendar table and time intelligence functions. This method is robust and adapts automatically to changes in the calendar or data, making it the optimal solution for calculating YTD metrics.

Best regards,

Re: Current year YTD and Last year YTD DAX Formula

Bertje123456 — Thu, 09 Jan 2025 13:52:20 GMT

Can I ask you another question?

I would like to add a slicer, so the user has the option to select year as a filter.

I would like them to be able to choose the year 2024. That they can see the YTD data from 2024 and previous year 2023.

How can I best do that?

Now I tried to add a slicer and I put the field 'jaar' (Year) in it. But nothing changed in the visuals.

Because now there is no data in 2025 yet, so there is nothing to see. I want them to go back a year.

This is my DAX formula for current year YTD

CY YTD =

CALCULATE(

SUM('VW_F_NETVERLIES_VOLUMES'[Verbruik in kWh]),

KEEPFILTERS('VW_D_DATUM_PERSISTANT'[Datum] <= EOMONTH(TODAY(), -1)),

'VW_D_DATUM_PERSISTANT'[Jaar] = YEAR(TODAY())

)

Previous year YTD

PY YTD =

CALCULATE(

SUM('VW_F_NETVERLIES_VOLUMES'[Verbruik in kWh]),

KEEPFILTERS('VW_D_DATUM_PERSISTANT'[Datum] <= EOMONTH(TODAY(), -1)),

'VW_D_DATUM_PERSISTANT'[Jaar] = YEAR(TODAY())-1

)

Re: Current year YTD and Last year YTD DAX Formula

Bertje123456 — Thu, 09 Jan 2025 13:57:53 GMT

Can I ask you another question?

I would like to add a slicer, so the user has the option to select year as a filter.

I would like them to be able to choose the year 2024. That they can see the YTD data from 2024 and previous year 2023.

How can I best do that?

Now I tried to add a slicer and I put the field 'jaar' (Year) in it. But nothing changed in the visuals.

Because now there is no data in 2025 yet, so there is nothing to see. I want them to go back a year.

This is my DAX formula for current year YTD

CY YTD =

CALCULATE(

SUM('VW_F_NETVERLIES_VOLUMES'[Verbruik in kWh]),

KEEPFILTERS('VW_D_DATUM_PERSISTANT'[Datum] <= EOMONTH(TODAY(), -1)),

'VW_D_DATUM_PERSISTANT'[Jaar] = YEAR(TODAY())

)

Previous year YTD

PY YTD =

CALCULATE(

SUM('VW_F_NETVERLIES_VOLUMES'[Verbruik in kWh]),

KEEPFILTERS('VW_D_DATUM_PERSISTANT'[Datum] <= EOMONTH(TODAY(), -1)),

'VW_D_DATUM_PERSISTANT'[Jaar] = YEAR(TODAY())-1

)

Re: Current year YTD and Last year YTD DAX Formula

Bertje123456 — Thu, 09 Jan 2025 13:58:40 GMT

Can I ask you another question?

I would like to add a slicer, so the user has the option to select year as a filter.

I would like them to be able to choose the year 2024. That they can see the YTD data from 2024 and previous year 2023.

How can I best do that?

Now I tried to add a slicer and I put the field 'jaar' (Year) in it. But nothing changed in the visuals.

Because now there is no data in 2025 yet, so there is nothing to see. I want them to go back a year.

This is my DAX formula for current year YTD

CY YTD =

CALCULATE(

SUM('VW_F_NETVERLIES_VOLUMES'[Verbruik in kWh]),

KEEPFILTERS('VW_D_DATUM_PERSISTANT'[Datum] <= EOMONTH(TODAY(), -1)),

'VW_D_DATUM_PERSISTANT'[Jaar] = YEAR(TODAY())

)

Previous year YTD

PY YTD =

CALCULATE(

SUM('VW_F_NETVERLIES_VOLUMES'[Verbruik in kWh]),

KEEPFILTERS('VW_D_DATUM_PERSISTANT'[Datum] <= EOMONTH(TODAY(), -1)),

'VW_D_DATUM_PERSISTANT'[Jaar] = YEAR(TODAY())-1

)