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 ma...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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'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.