Forum Discussion
Edmundo
4 years agoRegular Visitor
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,...
- 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_conditionI hope that helps,
ibarrau
Super User
4 years agoHi. 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,