Forum Discussion

pstrachn's avatar
pstrachn
Regular Visitor
3 years ago

Calculation based on current year

Hi,

 

I would like some help with an expression to calculate a variance based on checking which year we are currently in. I was thinking that an if statement could work here, but was not able to make this work yet.

I have the below express that currently calculates a forecast based on completed months. I'm running into an issue when the year flips over since there's no reference to year in my data. So what I've done is created a new column with value of 2022 to specify last year.

 

So I would like the below formula to run if we are in the current year. 

CapExYTDF = CALCULATE(SUM('F22 CapEx Data'[Budget Amount]), 'F22 CapEx Data'[Month Number] < month(today()))

 

If we are in anything other than the current year, I would like the below calculation to be used: 

CapExYTDF = CALCULATE(SUM('F22 CapEx Data'[Budget Amount])

Need Help

3 Replies

    • pstrachn's avatar
      pstrachn
      Regular Visitor
      3 years ago

      Thank you for your reply. Please see below dummy data structure.

       

      1) So I would like the below formula to run if we are in the current year (as it relates to the Year column in table below. In this case the value in the year column is 2022, so the check would return a false result and should use the formula in step 2). 

      CapExYTDF = CALCULATE(SUM('F22 CapEx Data'[Budget Amount]), 'F22 CapEx Data'[Month Number] < month(today()))

       

      2) If we are in anything other than the current year (as it relates to the Year column in data below), I would like the below calculation to be used: 

      CapExYTDF = CALCULATE(SUM('F22 CapEx Data'[Budget Amount])

       

       

      DescriptionBudget AmountMonth NumberYear
      Line Item 12500052022
      Line Item 2200062022
      Line Item 335000122022
      Line Item 4100042022
      Line Item 55600092022
      Line Item 6500042022
      Line Item 76300082022
      Line Item 8652032022
      Line Item 91562102022
      Line Item 101655332022
      Line Item 11986382022
    • pstrachn's avatar
      pstrachn
      Regular Visitor
      3 years ago

      Hi,

       

      Wanted to follow up and see if anyone had any suggestions on this? If there's anything other information i can provide, please let me know.