Forum Discussion

Edmundo's avatar
Edmundo
Regular Visitor
4 years ago
Solved

IF function that depends on month number

Hello everyone,   I need to calculate Return on assets ratio as annual but don't know how to write IF funcion that depends on month number in table below    ROA = DIVIDE(CALCULATE(SUM(UK_DB[Sum,...
  • ibarrau's avatar
    4 years ago

    Hi. I'll help you with the IF statement taking actual/current date regarding the row context of a visualization. I have added comments to help you. It would be a good idea to use variables. You can find your IF with variables and the __better_if that is kind of a better solution for that in case you want to replace it.

     

    ROA = 
    VAR __current_date = SELECTEDVALUE( Table[Data] ) //Your table example specifies column data as date. This gets the current date in row context
    VAR __month = MONTH (__current_date) //if your column is type date you can do this to get the month
    VAR __if_condition = 
        IF(__month = 3, 4 
            , IF(__month = 6, 2
                , IF(__month = 9, 1.3333, 1
                )
            )
        )
    VAR __better_if = // this would be a better way to handle the if
    SWITCH (__month
        , 3, 4
        , 6, 2
        , 9, 1.333
        ,1
    )
    RETURN
    DIVIDE(
        CALCULATE( SUM( UK_DB[Sum, Eur] ), UK_DB[Item] = "Net profit" )
        , CALCULATE( SUM(UK_DB[Sum, Eur]), UK_DB[Item] = "Assets" )
    ) * __if_condition

     

     I hope that helps,