Switch statement with two independent selected values

[a119526]

Hi - 

I am putting together a PVM analysis that currently compares Year-Over-Year variances for certain products. I want to add an additional selection box (similar to Sales Type in the top right) that will allow a user to select YTD, MoM, YoY to update the value we are comparing against. 

I included my current formula for Sales LY below the image. Can I update this formula to include an additional SELECTEDVALUE statement that will also look at the YoY,YTD,MoM selection and then calculate the starting point (sameperiodlastyear, endofmonth(previousmonth), etc.)?

Sales LY =

VAR Selection = SELECTEDVALUE('Sales Type'[Sales Type])

RETURN

SWITCH(TRUE(),

Selection = "Contract Sales",CALCULATE(SUM('SAP Sales Daily'[Contract_Sales]),SAMEPERIODLASTYEAR('Date'[Date])),

Selection = "Invoice Sales",CALCULATE(-SUM('SAP Sales Daily'[Gross_Sales]),SAMEPERIODLASTYEAR('Date'[Date])),

Selection = "WAC Sales", CALCULATE(SUM('SAP Sales Daily'[Wac_Sales]),SAMEPERIODLASTYEAR('Date'[Date]))

)

[AlexisOlson]

This is probably a good use case for Calculation Groups but you can probably get away with those with some SWITCH functions.

Something like this perhaps:

Sales LY =
VAR SalesType = SELECTEDVALUE ( 'Sales Type'[Sales Type] )
VAR PeriodType = SELECTEDVALUE ( 'Period Type'[Period Type] )
VAR PeriodDates =
    SWITCH (
        PeriodType,
        "LY", SAMEPERIODLASTYEAR ( 'Date'[Date] ),
        "YTD", DATESYTD ( 'Date'[Date] )
        /* Add more cases as desired. */
    )
RETURN
    CALCULATE (
        SWITCH (
            SalesType,
            "Contract Sales", SUM ( 'SAP Sales Daily'[Contract_Sales] ),
            "Invoice Sales", - SUM ( 'SAP Sales Daily'[Gross_Sales] ),
            "WAC Sales", SUM ( 'SAP Sales Daily'[Wac_Sales] )
        ),
        PeriodDates
    )

[a119526]

Thanks for  your response Alex.

I put together the following DAX, but am getting an error. 

"The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column."

Sales LY test =

VAR SelectionSales = SELECTEDVALUE('Sales Type'[Sales Type])
VAR DateType = SELECTEDVALUE('Date Type'[Date Type])
VAR Dates =
SWITCH(DateType,
"YTD",ENDOFYEAR(PREVIOUSYEAR('Date'[Date])),
"MoM",PREVIOUSMONTH('Date'[Date]),
"YoY",SAMEPERIODLASTYEAR('Date'[Date])
)

RETURN
CALCULATE(
SWITCH(SelectionSales,
"Contract Sales",CALCULATE(SUM('SAP Sales Daily'[Contract_Sales])),
"Invoice Sales",CALCULATE(-SUM('SAP Sales Daily'[Gross_Sales])),
"WAC Sales", CALCULATE(SUM('SAP Sales Daily'[Wac_Sales])))
,Dates)

[AlexisOlson]

Hmm. It looks like it isn't recognizing the Dates variable as a table argument. Does it help at all if you wrap each of the Dates cases with CALCULATETABLE, e.g. "MoM", CALCULATETABLE ( PREVIOUSMONTH ( 'Date'[Date ) ), and/or move the Dates argument inside each SelectionSales case instead of the outside?

[a119526]

Unfortunately neither worked. 

The top section before the RETURN works as expected, but the second SWITCH does not want to work with the 'DATES' filter. I also tried creating a different measure and using that as a filter, that didn't work either.

I wonder if I can do a series of IF statements? 

[a119526]

Ok, I think I figured it out. Probably not the most elegant solution, but it works.

Sales LY test = 

VAR SelectionSales = SELECTEDVALUE('Sales Type'[Sales Type])
VAR DateType = SELECTEDVALUE('Date Type'[Date Type])

RETURN
IF(DateType = "YTD",
        SWITCH(SelectionSales,
        "Contract Sales",CALCULATE(SUM('SAP Sales Daily'[Contract_Sales]),ENDOFYEAR(PREVIOUSYEAR('Date'[Date]))),
        "Invoice Sales",CALCULATE(-SUM('SAP Sales Daily'[Gross_Sales]),ENDOFYEAR(PREVIOUSYEAR('Date'[Date]))),
        "WAC Sales", CALCULATE(SUM('SAP Sales Daily'[Wac_Sales]),ENDOFYEAR(PREVIOUSYEAR('Date'[Date])))),
IF(DateType = "MoM",
        SWITCH(SelectionSales,
        "Contract Sales",CALCULATE(SUM('SAP Sales Daily'[Contract_Sales]),ENDOFMONTH(PREVIOUSMONTH('Date'[Date]))),
        "Invoice Sales",CALCULATE(-SUM('SAP Sales Daily'[Gross_Sales]),ENDOFMONTH(PREVIOUSMONTH('Date'[Date]))),
        "WAC Sales", CALCULATE(SUM('SAP Sales Daily'[Wac_Sales]),ENDOFMONTH(PREVIOUSMONTH('Date'[Date])))),
IF(DateType = "YoY",
        SWITCH(SelectionSales,
        "Contract Sales",CALCULATE(SUM('SAP Sales Daily'[Contract_Sales]),ENDOFMONTH(SAMEPERIODLASTYEAR('Date'[Date]))),
        "Invoice Sales",CALCULATE(-SUM('SAP Sales Daily'[Gross_Sales]),ENDOFMONTH(SAMEPERIODLASTYEAR('Date'[Date]))),
        "WAC Sales", CALCULATE(SUM('SAP Sales Daily'[Wac_Sales]),ENDOFMONTH(SAMEPERIODLASTYEAR('Date'[Date]))))
)))

[AlexisOlson]

Yeah, worst case you have to define all 9 possibilities. I think I could get something like my initial suggestion working but it would take some tinkering.

If you do go with 9 cases, you can also do it without nesting like this:

Sales LY test =
VAR SelectionSales = SELECTEDVALUE ( 'Sales Type'[Sales Type] )
VAR DateType = SELECTEDVALUE ( 'Date Type'[Date Type] )
RETURN
    SWITCH (
        TRUE (),
        DateType = "YTD" && SelectionSales = "Contract Sales",
            CALCULATE (
                SUM ( 'SAP Sales Daily'[Contract_Sales] ),
                ENDOFYEAR ( PREVIOUSYEAR ( 'Date'[Date] ) )
            ),
        DateType = "YTD" && SelectionSales = "Invoice Sales",
            CALCULATE (
                - SUM ( 'SAP Sales Daily'[Gross_Sales] ),
                ENDOFYEAR ( PREVIOUSYEAR ( 'Date'[Date] ) )
            ),
        DateType = "YTD" && SelectionSales = "WAC Sales",
            CALCULATE (
                SUM ( 'SAP Sales Daily'[Wac_Sales] ),
                ENDOFYEAR ( PREVIOUSYEAR ( 'Date'[Date] ) )
            ),
        DateType = "MoM" && SelectionSales = "Contract Sales",
            CALCULATE (
                SUM ( 'SAP Sales Daily'[Contract_Sales] ),
                ENDOFMONTH ( PREVIOUSMONTH ( 'Date'[Date] ) )
            ),
        DateType = "MoM" && SelectionSales = "Invoice Sales",
            CALCULATE (
                - SUM ( 'SAP Sales Daily'[Gross_Sales] ),
                ENDOFMONTH ( PREVIOUSMONTH ( 'Date'[Date] ) )
            ),
        DateType = "MoM" && SelectionSales = "WAC Sales",
            CALCULATE (
                SUM ( 'SAP Sales Daily'[Wac_Sales] ),
                ENDOFMONTH ( PREVIOUSMONTH ( 'Date'[Date] ) )
            ),
        DateType = "YoY" && SelectionSales = "Contract Sales",
            CALCULATE (
                SUM ( 'SAP Sales Daily'[Contract_Sales] ),
                ENDOFMONTH ( SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
            ),
        DateType = "YoY" && SelectionSales = "Invoice Sales",
            CALCULATE (
                - SUM ( 'SAP Sales Daily'[Gross_Sales] ),
                ENDOFMONTH ( SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
            ),
        DateType = "YoY" && SelectionSales = "WAC Sales",
            CALCULATE (
                SUM ( 'SAP Sales Daily'[Wac_Sales] ),
                ENDOFMONTH ( SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
            )
    )

[a119526]

Thanks so much for this. 

Just curious, would this solution perform better than the nested IF statements? They both give the same answers.

[AlexisOlson]

I'd expect them to be about the same but can't say for sure.

The reason I'd consider it has more to do with the fact that nested logic tends to be a bit more difficult to read, in general. I prefer to not be mentally matching parentheses if I don't have to.