Forum Discussion
a119526
5 years agoFrequent Visitor
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...
AlexisOlson
Super User
5 years agoYeah, 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
5 years agoFrequent Visitor
Thanks so much for this.
Just curious, would this solution perform better than the nested IF statements? They both give the same answers.