Is it possible to use If statement to select a measure?

Hi
I am trying to create a visual were we compare current year to date sales to what we call past years avg. to date. The problem I am running into is that the measure I created is applying the formula to all sales and not just past years. My solution would be to create an if statement that applies the formula only to prior year data. I am not sure if this is possible and if possible how to do it.

Prior Year YTD Sales Formula
YTD Average Sales = Total Sales / total workdays current year * workdays year to date current year

Hello @lpretorius,

Thank You for posting your query onto the Forum.

Well thereâ€™re lotâ€™s and lotâ€™s of content available onto our education portal as well as YouTube channel. You can surf the videos pertaining to this topic. Below are some of the links of the articles as well as videos provided for the reference.

For more concrete solution, please provide the working PBIX file for the reference alongwith the description of the results that youâ€™re trying to achieve so that members of the forum can assist you in a better and efficient manner.

Hoping you find this useful and meets your requirements that youâ€™ve been looking for.

Thanks and Warm Regards,
Harsh

I have uploaded the pbix with Text box describing problem

OnePageTest.pbix (322.9 KB)

Hello @lpretorius,

Thank You for posting your PBIX file alongwith the description of the results that youâ€™re trying to achieve.

Hereâ€™re the simple formulas youâ€™ll be required to write to achieve this results -

``````Switch Measure - Harsh =
SWITCH( TRUE() ,
SELECTEDVALUE( Dates[Year] ) = "2021" , [Total Sales] ,
[Total Sales AVG YTD] )

Switch Measure - Harsh - Totals =
SUMX(
SUMMARIZE(
TransactionList ,
Dates[Year] ,
ProductList[Type] ,
"@Totals" ,
[Switch Measure - Harsh] ) ,
[@Totals]
)
``````

First formula is written to achieve the results at a row level and the second formula is written to achieve the correct results of the grand totals as well.

Iâ€™m attaching the PBIX file for the reference so that you can go through it as well as providing few of the links videos based on the concepts applied in it to achieve this results.

Hoping you find this useful and meets your requirements that youâ€™ve been looking for.

Note To view the below post of â€śFix Incorrect Totalsâ€ť please click onto the link and not onto the â€śexpand/collapseâ€ť button.

Thanks and Warm Regards,
Harsh

OnePageTest - Harsh.pbix (323.8 KB)

Thank you

(I see @Harsh has already posted while I was composing my response, but thought Iâ€™d add it anyway.)

Yes you can use an IF statement to choose a different measure. Here are your existing matrices and my new one:

Hereâ€™s the measure I used:

``````New Sales =
VAR _CurrentDateYear = YEAR( TODAY() )
VAR _CurrentColumnYear = VALUE( SELECTEDVALUE( Dates[Year] ) )
VAR _Result = IF(
_CurrentColumnYear = _CurrentDateYear,
[Total Sales],
[Total Sales AVG YTD]
)

RETURN
_Result
``````

Hope it helps.
Greg
eDNA Forum - Different Measure.pbix (323.7 KB)

1 Like