Forum Discussion
IF statement using row in matrix
Hello,
I am creating a matrix to show a breakdown of orders and the YOY completion percentage of this year compared to last year.
The YOY calculation is a measure defined as follows:
YOY = CALCULATE(SUM(Query2[ORDERS]),Query2[YEAR] = 2021)/CALCULATE(SUM(Query2[ORDERS]),Query2[YEAR] = 2020)
But I want this measure to be blank for the previous year and want to use an if statment in my measure to return "" when the year in the row is minimum year present in the data.
I've tried to use something like the following:
YOY = IF(Query2[YEAR] = MIN(Query2[YEAR]),"",CALCULATE(SUM(Query2[ORDERS]),Query2[YEAR] = 2021)/CALCULATE(SUM(Query2[ORDERS]),Query2[YEAR] = 2020)) But that formula is not accepted by DAX/Power Bi. How can I create a measure that references a value in a row in a matrix (i.e. a column in my data represented as a row header)?
How about this instead?
YOY = VAR RowYear = MAX( Query2[YEAR] ) RETURN DIVIDE ( SUM ( Query2[ORDERS] ), CALCULATE ( SUM ( Query2[ORDERS] ), Query2[ORDERS] = RowYear - 1 ) )
1 Reply
- AlexisOlson
Super User
How about this instead?
YOY = VAR RowYear = MAX( Query2[YEAR] ) RETURN DIVIDE ( SUM ( Query2[ORDERS] ), CALCULATE ( SUM ( Query2[ORDERS] ), Query2[ORDERS] = RowYear - 1 ) )