topic Re: Calculate YTD and YTD-1 for the same period in DAX Commands and Tips

Calculate YTD and YTD-1 for the same period

AxelKAp — Tue, 19 Oct 2021 15:55:49 GMT

Hello,

I would like to calculate the YTD Spend and the YTD -1 Spend for the same period.
For example for 2021 we are in October so I would like to calculate the Spend for 2020 only until October.

For the moment I have created this formula :

Spend YTD-1 = CALCULATE(SUM('Spend consolidé'[Invoice_Accounting_EUR]),SAMEPERIODLASTYEAR('Calendar'[Date]))

But it's not working, when I filter on 2021, with this formula I have the spend for the entire year of 2020 instead of having until October.

Any advice to help me solve my problem ?

Thanks,

Axel

Re: Calculate YTD and YTD-1 for the same period

smpa01 — Tue, 19 Oct 2021 17:50:17 GMT

AxelKAp can you try this for YTD calculation

YTDSumCY= CALCULATE ( SUM ( 'Spend consolidé'[Invoice_Accounting_EUR] ), 'Calendar'[Calendar_Date] <= MAX ( 'Spend consolidé'[Date] ), ALL ( 'Calendar'[Calendar_Date] ) ) YTDSumPY = CALCULATE ([YTDSumCY], SAMEPERIODLASTYEAR('Calendar'[Calendar_Date]))

Re: Calculate YTD and YTD-1 for the same period

CNENFRNL — Wed, 20 Oct 2021 01:14:52 GMT

Spend LYTD = CALCULATE( SUM( 'Spend consolidé'[Invoice_Accounting_EUR] ), DATEADD( DATESYTD( 'Calendar'[Date] ), -1, YEAR ) )

Re: Calculate YTD and YTD-1 for the same period

AxelKAp — Wed, 20 Oct 2021 11:11:45 GMT

Hi, I try your formula but it's not working, this formula returns me the whole previous year

Re: Calculate YTD and YTD-1 for the same period

AxelKAp — Wed, 20 Oct 2021 11:13:14 GMT

Hi, I have an error with ths formula, Power BI say me that I can't use MAX() with a TRUE/FALSE condition

Re: Calculate YTD and YTD-1 for the same period

i26923 — Thu, 27 Apr 2023 14:25:09 GMT

Finally this worked for me!

Spend YTD = CALCULATE(SUM('TableName'[FieldName]), DATESYTD('DateTableName'[Date]))

Spend YTD-1 = CALCULATE([Spend YTD], SAMEPERIODLASTYEAR(DATESYTD('DateTableName'[Date])))

I think the clue was filtering by DATESYTD.