Calculation based on current year

[pstrachn]

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

[Anonymous]

Hi @pstrachn,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[pstrachn]

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.

[pstrachn]

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

Description	Budget Amount	Month Number	Year
Line Item 1	25000	5	2022
Line Item 2	2000	6	2022
Line Item 3	35000	12	2022
Line Item 4	1000	4	2022
Line Item 5	56000	9	2022
Line Item 6	5000	4	2022
Line Item 7	63000	8	2022
Line Item 8	6520	3	2022
Line Item 9	1562	10	2022
Line Item 10	16553	3	2022
Line Item 11	9863	8	2022