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

Helper I

## Calculate YTD and YTD-1 for the same period

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

5 REPLIES 5
New Member

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.

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

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
Helper I

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

Super User

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

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Helper I

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

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.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors