cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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!

New Animated Dashboard: Sales Calendar

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors