Forum Discussion

Regular Visitor

9 months ago

Solved

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

I am attempting to display data in a table visual based on the week selected in a slicer, but also display the previous week's data without the user having to select both weeks. The week data is a whole number and not date related/formatted.

A sample of the data on the origin table looks like this:

If the user selects 202530 from the slicer, then the desired outcome would look like this:

I've attempted several approaches, but can't get them to quite work. Thanks for the help!

Need Help

Praful_Potphode

9 months ago

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

v-veshwara-msft
9 months ago
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.

8 Replies

danextian
Super User
9 months ago
Hi LThib

If you want the week number to appear, use a disconnected dates table with a measure to control the value and which weeks are shown. To apply this to multiple measures, you can use a calculation group or create separate measures. Use dynamic format strings to show different number formats (currency, whole number and/or percentage).

Please see the attached sample pbix.
show last two week ending date with difference.pbix761 KB
parry2k
Super User
9 months ago
LThib

As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist

There are a lot of blogs/videos to get the Previous week's data, the core is to work with the date dimension.

Praful_Potphode

Super User

9 months ago

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

LThib
Regular Visitor
9 months ago
I may not have done this correctly. I created the table as you indicated, then created measures in the same table using Current Week Value and Previous Week Value. When I put Business Segment and Metric in the rows for the matrix visual along with the current and previous values in the values section, nothing returns. When I move either the Business Segment or Metric to the Columns section of the matrix, the current and previous week values are shown.
- v-veshwara-msft
  Community Support
  9 months ago
  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.

v-veshwara-msft
Community Support
9 months ago
Hi LThib ,
Thanks for reaching out to Microsoft Fabric Community.

Just checking in to see if you query is resolved and if any responses were helpful.
Otherwise, feel free to reach out for further assistance.

Thanks to community members for sharing your valuable guidance and continued support.

Thank you.
v-veshwara-msft
Community Support
9 months ago
Hi LThib ,
Just wanted to check if the responses provided were helpful in resolving your query. If further assistance is needed, please reach out.
Thank you.

Forum Discussion

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

8 Replies

Related content

Display Selected and not selected values

Previous month selected automatically from drop-down slicer

Display Current Selected year vs Previous year

Diff between selected and previous period

How to Display Selected Month Plus Previous Two Months Dynamically in Power BI Visuals?