Forum Discussion

PaulSB's avatar
PaulSB
Regular Visitor
4 years ago
Solved

DAX Formula Conditional on Fiscal Year

I have a Calendar table with Fiscal Year column.  I'm trying to write a DAX formula to add a value to a measure according to the Fiscal Year.

Cost = IF('Calendar'[Fiscal Year] = 2020, 'Fixed'[Basis] + 20, IF('Calendar'[Fiscal Year] = 2021, 'Fixed'[Basis] + 25,100)

How can I coorect this?

  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, PaulSB 

     

    If [Basis] is a measure. You can try the following methods.

    Cost = 
IF (
    SELECTEDVALUE ( 'Calendar'[Fiscal Year] ) = 2020,
    'Fixed'[Basis] + 20,
    IF (
        SELECTEDVALUE ( 'Calendar'[Fiscal Year] ) = 2021,
        'Fixed'[Basis] + 25,
        100
    )
)

     

    If [Basis] is a column. You can try the following methods.

    Cost 2 = 
IF (
    SELECTEDVALUE ( 'Calendar'[Fiscal Year] ) = 2020,
    SUM('Fixed'[Basis 2]) + 20,
    IF (
        SELECTEDVALUE ( 'Calendar'[Fiscal Year] ) = 2021,
        SUM('Fixed'[Basis 2]) + 25,
        100
    )
)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Adescrit's avatar
    Adescrit
    Icon for Impactful Individual rankImpactful Individual
    4 years ago

    Hi PaulSB ,

     

    Is [Basis] a measure or a column?

     

    Are you receiving an error message, or an incorrect result?

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, PaulSB 

     

    If [Basis] is a measure. You can try the following methods.

    Cost = 
IF (
    SELECTEDVALUE ( 'Calendar'[Fiscal Year] ) = 2020,
    'Fixed'[Basis] + 20,
    IF (
        SELECTEDVALUE ( 'Calendar'[Fiscal Year] ) = 2021,
        'Fixed'[Basis] + 25,
        100
    )
)

     

    If [Basis] is a column. You can try the following methods.

    Cost 2 = 
IF (
    SELECTEDVALUE ( 'Calendar'[Fiscal Year] ) = 2020,
    SUM('Fixed'[Basis 2]) + 20,
    IF (
        SELECTEDVALUE ( 'Calendar'[Fiscal Year] ) = 2021,
        SUM('Fixed'[Basis 2]) + 25,
        100
    )
)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • PaulSB's avatar
      PaulSB
      Regular Visitor
      4 years ago

      Thank You Charlotte,  That does exactly what was required.

      Regards,

      Paul