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!