Skip to main content
Showing results for 
Search instead 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

Post Prodigy
Post Prodigy

Calculated 2 years previous



Does anyone know how I would write a correct formula?


Year 2 = CALCULATE([YTD Budget],ALLSELECTED('Calendar'[Year]) -2,ALL('Calendar'[Month]))


In the above formula I want to be able to select any year in the calendar table and have the formula go back 2 years in time and grab that entire years budget.


Adding the -2  breaks th eformula but you can see what I was trying to do. What should I be using?



Community Champion
Community Champion



YTD From 2 Ago =
CALCULATE ( [YTD Budget], DATEADD ( 'Calendar'[Date], - 2, YEAR ) )

If [YTD Budget] uses TOTALYTD - then the above will give you the YTD Total but from 2 years ago! Smiley Happy



My YTD budget is useing:


YTD Budget = IF(SUM('00-GLSummary'[Revenue]) > 0,  Abs(SUM('00-Budget'[BUDGETAMT])),sum('00-Budget'[BUDGETAMT]))


So this formula is blank for now. I am going to try and convert my YTD Budget to use TotalYTD


That sounds like a more sdtandard way to calculate amounts anyway.



I changed my formula to:


IF(SUM('00-GLSummary'[Revenue]) > 0,  TOTALYTD(Sum('00-Budget'[BUDGETAMT]),'Calendar'[Date]),sum('00-Budget'[BUDGETAMT]))


and the YTD From 2 Ago = CALCULATE ( [YTD Budget], DATEADD ( 'Calendar'[Date], - 2, YEAR ) )


is still blank.

Hi @lcasey


Just to confirm that you do not have any filters in the Visual, Page or Report levels?


What I would suggest to troubleshoot, is to create a table and put the indivudual measures into the table with your dates. In doing so this can help visually see where the possible error is occuring.


I have done that in the past, which has helped me identify where I was going wrong.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Power BI Blog

Helpful resources

Europe Fabric Conference

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.