cancel
Showing results for
Did you mean:
Post Prodigy

## Calculated 2 years previous

Hello,

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?

4 REPLIES 4
Community Champion

@lcasey

```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!

Post Prodigy

Thanks,

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.

Post Prodigy

Weird,

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.

Super User

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

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors