SWITCH based on date slicer

[StaceyG]

Hello,

I have a date table like below:

The report has a slicer for Month ('Date'[MonthEndDate]).  The report has a column for calculations for the selected month, and the month prior to the one selected.  Below is the switch statement for the month selected, which calculates and displays correctly:

COLT_Month =

VAR MonthEndDate = SELECTEDVALUE('Date'[MonthEndDate])

VAR TotalTrx = CALCULATE(SUM(MonthlyTrx[TOTAL_TRANSACTIONS]), FILTER('MonthlyTrx', 'MonthlyTrx'[MonthEndDate]=MonthEndDate))

VAR FraudTrx = CALCULATE(SUM('MonthlyTrx'[FAILED]), FILTER(MonthlyTrx, MonthlyTrx[MonthEndDate]=MonthEndDate))

VAR DecisionTrx = CALCULATE((SUM(MonthlyTrx[EXPIRED_ID])+sum(MonthlyTrx[FAILED])+sum(MonthlyTrx[SUBMISSION_ERROR])+sum(MonthlyTrx[UNSUPPORTED_ID])+sum(MonthlyTrx[POTENTIAL_PAPER_ID])+sum(MonthlyTrx[VERIFIED])), FILTER(MonthlyTrx, MonthlyTrx[MonthEndDate]=MonthEndDate))

VAR DecisionPct = CALCULATE(DIVIDE(DecisionTrx, TotalTrx))

VAR UnableTrx = CALCULATE(sum(MonthlyTrx[RECAPTURE_ID]), FILTER(MonthlyTrx, MonthlyTrx[MonthEndDate]=MonthEndDate))

VAR UnablePct = CALCULATE(DIVIDE(UnableTrx, TotalTrx))

VAR ACAR = CALCULATE(DIVIDE(TotalTrx, DecisionTrx))

VAR AvgLatency = CALCULATE(AVERAGE(MonthlyTrx[AVG_LATENCY]), FILTER(MonthlyTrx, MonthlyTrx[MonthEndDate]=MonthEndDate))

VAR AvgTPM = CALCULATE(AVERAGE(MonthlyTrx[AVG_TPM]), FILTER(MonthlyTrx, MonthlyTrx[MonthEndDate]=MonthEndDate))

VAR AvgTPS = CALCULATE(AVERAGE(MonthlyTrx[AVG_TPS]), FILTER(MonthlyTrx, MonthlyTrx[MonthEndDate]=MonthEndDate))

VAR PeakTPM = CALCULATE(MAX(MonthlyTrx[PEAK_MINUTE]), FILTER(MonthlyTrx, MonthlyTrx[MonthEndDate]=MonthEndDate))

VAR PeakTPS = CALCULATE(MAX(MonthlyTrx[PEAK_SECOND]), FILTER(MonthlyTrx, MonthlyTrx[MonthEndDate]=MonthEndDate))

VAR Uptime = CALCULATE(DIVIDE(SUM(UptimeDowntime[UptimeMinutes]), SUM(UptimeDowntime[MONTHLY_OPERATING_MINUTES])),FILTER(UptimeDowntime, UptimeDowntime[MONTH]=MonthEndDate))

RETURN

SWITCH (

TRUE (),

MAX('Report Config'[Var]) = "Total", FORMAT(TotalTrx, "#,###"),

MAX('Report Config'[Var]) = "Fraud", FORMAT(FraudTrx, "#,###"),

MAX('Report Config'[Var]) = "ACAR", FORMAT(ACAR, "0.00"),

MAX('Report Config'[Var]) = "Decision", FORMAT(DecisionPct, "0.00%"),

MAX('Report Config'[Var]) = "Recapture", FORMAT(UnablePct, "0.00%"),

MAX('Report Config'[Var]) = "AvgLatency", FORMAT(AvgLatency, "##.##"),

MAX('Report Config'[Var]) = "AvgTPM", FORMAT(AvgTPM, "#,###.##"),

MAX('Report Config'[Var]) = "AvgTPS", FORMAT(AvgTPS, "#,###.##"),

MAX('Report Config'[Var]) = "PeakTPM", FORMAT(PeakTPM, "#,###"),

MAX('Report Config'[Var]) = "PeakTPS", FORMAT(PeakTPS, "#,###"),

MAX('Report Config'[Var]) = "Uptime", FORMAT(Uptime, "0.00%")

)

Below is the switch statement for the prior month, which displays as blank no matter what month is selected from the slicer:

COLT_PriorMonth =

VAR MonthEndDate = IF(HASONEVALUE('Date'[MonthEndDate]), [PriorMonth])

VAR TotalTrx = CALCULATE(SUM(MonthlyTrx[TOTAL_TRANSACTIONS]), FILTER('MonthlyTrx', 'MonthlyTrx'[MonthEndDate]=MonthEndDate))

VAR FraudTrx = CALCULATE(SUM('MonthlyTrx'[FAILED]), FILTER(MonthlyTrx, MonthlyTrx[MonthEndDate]=MonthEndDate))

VAR DecisionTrx = CALCULATE((SUM(MonthlyTrx[EXPIRED_ID])+sum(MonthlyTrx[FAILED])+sum(MonthlyTrx[SUBMISSION_ERROR])+sum(MonthlyTrx[UNSUPPORTED_ID])+sum(MonthlyTrx[POTENTIAL_PAPER_ID])+sum(MonthlyTrx[VERIFIED])), FILTER(MonthlyTrx, MonthlyTrx[MonthEndDate]=MonthEndDate))

VAR DecisionPct = CALCULATE(DIVIDE(DecisionTrx, TotalTrx))

VAR UnableTrx = CALCULATE(sum(MonthlyTrx[RECAPTURE_ID]), FILTER(MonthlyTrx, MonthlyTrx[MonthEndDate]=MonthEndDate))

VAR UnablePct = CALCULATE(DIVIDE(UnableTrx, TotalTrx))

VAR ACAR = CALCULATE(DIVIDE(TotalTrx, DecisionTrx))

VAR AvgLatency = CALCULATE(AVERAGE(MonthlyTrx[AVG_LATENCY]), FILTER(MonthlyTrx, MonthlyTrx[MonthEndDate]=MonthEndDate))

VAR AvgTPM = CALCULATE(AVERAGE(MonthlyTrx[AVG_TPM]), FILTER(MonthlyTrx, MonthlyTrx[MonthEndDate]=MonthEndDate))

VAR AvgTPS = CALCULATE(AVERAGE(MonthlyTrx[AVG_TPS]), FILTER(MonthlyTrx, MonthlyTrx[MonthEndDate]=MonthEndDate))

VAR PeakTPM = CALCULATE(MAX(MonthlyTrx[PEAK_MINUTE]), FILTER(MonthlyTrx, MonthlyTrx[MonthEndDate]=MonthEndDate))

VAR PeakTPS = CALCULATE(MAX(MonthlyTrx[PEAK_SECOND]), FILTER(MonthlyTrx, MonthlyTrx[MonthEndDate]=MonthEndDate))

VAR Uptime = CALCULATE(DIVIDE(SUM(UptimeDowntime[UptimeMinutes]), SUM(UptimeDowntime[MONTHLY_OPERATING_MINUTES])),FILTER(UptimeDowntime, UptimeDowntime[MONTH]=MonthEndDate))

RETURN

SWITCH (

TRUE (),

MAX('Report Config'[Var]) = "Total", FORMAT(TotalTrx, "#,###"),

MAX('Report Config'[Var]) = "Fraud", FORMAT(FraudTrx, "#,###"),

MAX('Report Config'[Var]) = "ACAR", FORMAT(ACAR, "0.00"),

MAX('Report Config'[Var]) = "Decision", FORMAT(DecisionPct, "0.00%"),

MAX('Report Config'[Var]) = "Recapture", FORMAT(UnablePct, "0.00%"),

MAX('Report Config'[Var]) = "AvgLatency", FORMAT(AvgLatency, "##.##"),

MAX('Report Config'[Var]) = "AvgTPM", FORMAT(AvgTPM, "#,###.##"),

MAX('Report Config'[Var]) = "AvgTPS", FORMAT(AvgTPS, "#,###.##"),

MAX('Report Config'[Var]) = "PeakTPM", FORMAT(PeakTPM, "#,###"),

MAX('Report Config'[Var]) = "PeakTPS", FORMAT(PeakTPS, "#,###"),

MAX('Report Config'[Var]) = "Uptime", FORMAT(Uptime, "0.00%")

)

In the prior month switch statement, I have tried HASONEVALUE, ISFILTERED, and ISINSCOPE for the MonthEndDate variable.  This variable is the only difference between the two switch statements.  To confirm that it would return the correct date, I also built a measure:

PriorMonth = IF(ISFILTERED('Date'[MonthEndDate]), MAX('Date'[PriorMonthEndDate]))

I placed this measure in a card and, when I selected months from the slicer, it returned the correct month end date for the prior month.  So I am lost as to why the switch statement is returning blank values for the prior month column.

I appreciate any ideas or suggestions you may have!

[lbendlin · Accepted solution]

Change 

VAR MonthEndDate = IF(HASONEVALUE('Date'[MonthEndDate]), [PriorMonth])

to 

VAR MonthEndDate = SELECTEDVALUE('Date'[PriorMonthEndDate])

Also, you may want to consider using Calculation Groups.