Calculate YTD and YTD-1 for the same period

[AxelKAp]

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

[i26923]

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.

[CNENFRNL]

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

[AxelKAp]

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

[smpa01]

@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]))

[AxelKAp]

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