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

DAX DAX Calculations
1

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

2

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. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

3

I have uploaded the pbix with Text box describing problem

OnePageTest.pbix (322.9 KB)

4

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. :slightly_smiling_face:

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)

5

Thank you

6

Hi @lpretorius

(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:

eDNA Forum - Different Measure 1
eDNA Forum - Different Measure 11611Ă—766 109 KB

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