Table Visual - Display data from week selected in slicer and previous week

Hi LThib ,

First create a disconnected table(click New Table in DAX)  as shown below:

ComparisonTable = 
VAR CurrentWeek = MAX('YourTable'[Week])
VAR PreviousWeek = CurrentWeek - 1

RETURN
UNION(
    ROW("Business Segment", "Dry", "Metric", "Sum of Cases Early"),
    ROW("Business Segment", "Dry", "Metric", "Sum of Cases Late"),
    ROW("Business Segment", "Dry", "Metric", "Sum of Cases On Time"),
    ROW("Business Segment", "Frozen", "Metric", "Sum of Cases Early"),
    ROW("Business Segment", "Frozen", "Metric", "Sum of Cases Late"),
    ROW("Business Segment", "Frozen", "Metric", "Sum of Cases On Time")
)

then write a switch case expression to acheive the value.

Current Week Value = 
VAR SelectedMetric = SELECTEDVALUE(ComparisonTable[Metric])
VAR CurrentWeek = MAX('YourTable'[Week])
VAR Segment = SELECTEDVALUE(ComparisonTable[Business Segment])

RETURN
SWITCH(
    SelectedMetric,
    "Sum of Cases Early", 
        CALCULATE(SUM('YourTable'[Cases Early]), 
                  'YourTable'[Week] = CurrentWeek,
                  'YourTable'[Business Segment] = Segment),
    "Sum of Cases Late",
        CALCULATE(SUM('YourTable'[Cases Late]), 
                  'YourTable'[Week] = CurrentWeek,
                  'YourTable'[Business Segment] = Segment),
    "Sum of Cases On Time",
        CALCULATE(SUM('YourTable'[Cases On Time]), 
                  'YourTable'[Week] = CurrentWeek,
                  'YourTable'[Business Segment] = Segment)
)

Previous Week Value = 
VAR SelectedMetric = SELECTEDVALUE(ComparisonTable[Metric])
VAR PreviousWeek = MAX('YourTable'[Week]) - 1
VAR Segment = SELECTEDVALUE(ComparisonTable[Business Segment])

RETURN
SWITCH(
    SelectedMetric,
    "Sum of Cases Early", 
        CALCULATE(SUM('YourTable'[Cases Early]), 
                  'YourTable'[Week] = PreviousWeek,
                  'YourTable'[Business Segment] = Segment),
    "Sum of Cases Late",
        CALCULATE(SUM('YourTable'[Cases Late]), 
                  'YourTable'[Week] = PreviousWeek,
                  'YourTable'[Business Segment] = Segment),
    "Sum of Cases On Time",
        CALCULATE(SUM('YourTable'[Cases On Time]), 
                  'YourTable'[Week] = PreviousWeek,
                  'YourTable'[Business Segment] = Segment)
)

Now in matrix visual,drag and drop ComparisonTable[Business Segment],ComparisonTable[Metric] to Rows

and Current Week Value,PreviousWeek Value to Values.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

Hi LThib ,

Thanks for the update.

Yes, that behavior is expected. Since the measures use SELECTEDVALUE to detect the metric in the filter context, placing ComparisonTable[Metric] in the Columns section is required for the calculation to evaluate correctly.

The recommended Matrix setup is:

Rows:
• Business Segment

Columns:
• Metric

Values:
• Current Week Value
• Previous Week Value

This setup ensures both Business Segment and Metric are clearly defined in the visual context so the measures return results as designed.

If you are aiming for a different layout on the Matrix, please share a sample screenshot of the desired structure and we can provide guidance on adjusting the DAX accordingly.

Please reach out for further assistance.

Thank you.