Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
StaceyG
Helper I
Helper I

SWITCH based on date slicer

Hello,

 

I have a date table like below:

 

StaceyG_0-1635346755127.png

 

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!
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Change 

 

VAR MonthEndDate = IF(HASONEVALUE('Date'[MonthEndDate]), [PriorMonth])
 
to 
 
VAR MonthEndDate = SELECTEDVALUE('Date'[PriorMonthEndDate])
 
Also, you may want to consider using Calculation Groups.

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

Change 

 

VAR MonthEndDate = IF(HASONEVALUE('Date'[MonthEndDate]), [PriorMonth])
 
to 
 
VAR MonthEndDate = SELECTEDVALUE('Date'[PriorMonthEndDate])
 
Also, you may want to consider using Calculation Groups.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.