IF function that depends on month number

Edmundo — Fri, 22 Oct 2021 07:06:39 GMT

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, Eur]),UK_DB[Item]="Net profit"), CALCULATE(SUM(UK_DB[Sum, Eur]),UK_DB[Item]="Assets"))*IF([Date][month]=3;4;IF([Date][month]=6;2;IF([Date][month]=9;1.3333;1)

Could you please help me with the formula marked in red?

Re: IF function that depends on month number

ibarrau — Fri, 22 Oct 2021 13:10:05 GMT

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,

topic Re: IF function that depends on month number in DAX Commands and Tips

IF function that depends on month number

Re: IF function that depends on month number