Forum Discussion

a119526's avatar
a119526
Frequent Visitor
5 years ago

Switch statement with two independent selected values

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]))
)
Need Help

8 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      a119526
      Frequent Visitor
      5 years ago

      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's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        5 years ago

        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?