Dynamic rollback of the previous N weeks of data

[v-kongfanf-msft]

Scenario:

In this article, we'd like to create a report to display some date-related data and let users switch which previous weeks of data they want to see.
The detailed requirements are as follows:
1. Dynamically roll back to display the previous N weeks of data. Use a slicer to pick a week number for N.
2. Summarize the sum of the previous N weeks of data into a Monday display.
3. Dynamically select holidays in different countries and exclude the values of weekends and holidays from the summary.

Expected Result:

We have three slicers in the report: country, week number and Date. If country selects China, number selects 4, and Date selects 1/30/2023, it means to roll back 4 weeks of data from 1/30/2023 and aggregate the weekly data into each Monday. And the summary should exclude data generated by Saturdays, Sundays, and holidays within the current date range. For example, the portion of the red box is the eligible data, of which 1/2/ 2023 is the Chinese New Year holiday.

Raw Data:

Expected:

Sample Data:

In the sample data, there is a product table(All data for 2023).

How:

Steps:

1. For data preparation, get the Chinese and US 2023 holidays from the web and get the dates of WeekDays and prepare the data table for slicer generation. Prepare the following tables:

Date = VALUES('Product'[Date])  // Creating a virtual Date table to generate slicers

Country table:

PreviousWeek table:

AmericaHoliday table:

ChinaHoliday table:

2. Determine if the date in the date column is a holiday or is weekend based on the country selected.

WeekDay = WEEKDAY ( 'Product'[Date], 2 )

If the Date is Saturday or Sunday, return to 6 or 7:

If the Date is holiday, return to 1:

Is Holiday =
VAR _chooseCountry =
    SELECTEDVALUE ( Country[country] )
VAR __cur_date =
    SELECTEDVALUE ( 'Product'[Date] )
VAR __isWeekend =
    WEEKDAY ( __cur_date, 2 )
VAR _matchingCountries =
    SWITCH (
        _chooseCountry,
        "China",
            IF (
                COUNTROWS ( FILTER ( 'ChinaHoliday', 'ChinaHoliday'[Date] = __cur_date ) ) > 0,
                1,
                BLANK ()
            ),
        "America",
            IF (
                COUNTROWS ( FILTER ( 'AmericaHoliday', 'AmericaHoliday'[Date] = __cur_date ) ) > 0,
                1,
                BLANK ()
            )
    )
RETURN
    _matchingCountries

3. Determines if the current date is Monday, if so, dynamically rolls back the previous n weeks of data and aggregates the weekly data to Monday.

Create three measures as below:

Table_DateMeasure = CALCULATE ( MAX ( 'Product'[Date] ) )

IsMonday_Table =
IF ( WEEKDAY ( 'Product'[Table_DateMeasure], 2 ) = 1, 1, 0 )

Previous weeks =
IF (
    SELECTEDVALUE ( 'Product'[Date] )
        >= SELECTEDVALUE ( 'Date'[Date] )
            - SELECTEDVALUE ( PreviousWeek[number] ) * 7
        && SELECTEDVALUE ( 'Product'[Date] ) <= SELECTEDVALUE ( 'Date'[Date] ),
    1,
    IF ( NOT ISFILTERED ( 'Date'[Date] ), SELECTEDVALUE ( 'Product'[Date] ) )
)

Put the two measures IsMonday_Table and Previous weeks into the filter pane of the matrix and set both to 1:

4. Filter the dates and calculate the final sum of Absence and Accidents. Create two measures:

Sum Absence =
VAR __cur_date =
    SELECTEDVALUE ( 'Product'[Date] )
VAR __ismonday =
    WEEKDAY ( __cur_date, 2 )
VAR __start_date =
    IF ( __ismonday = 1, __cur_date )
VAR __end_date = __start_date + 6
VAR __isHoliday =
    IF ( [Is Holiday], __cur_date )
VAR __result =
    CALCULATE (
        SUM ( 'Product'[Absence] ),
        'Product'[Date] >= __start_date
            && 'Product'[Date] <= __end_date
            && 'Product'[WeekDay] <= 5
            && 'Product'[Date] <> __isHoliday
    )
RETURN
    __result

Sum Accidents =
VAR __cur_date =
    SELECTEDVALUE ( 'Product'[Date] )
VAR __ismonday =
    WEEKDAY ( __cur_date, 2 )
VAR __start_date =
    IF ( __ismonday = 1, __cur_date )
VAR __end_date = __start_date + 6
VAR __isHoliday =
    IF ( [Is Holiday], __cur_date )
VAR __result =
    CALCULATE (
        SUM ( 'Product'[Accidents] ),
        'Product'[Date] >= __start_date
            && 'Product'[Date] <= __end_date
            && 'Product'[WeekDay] <= 5
            && 'Product'[Date] <> __isHoliday
    )
RETURN
    __result

Put Sum Absence and Sum Accidents in the Values box of the matrix:

Select the matrix, and turn on the Switch values to row option in the Formatting pane:

Additional:

With the help of measure to determine whether the date is a holiday/weekend or not, we can avoid data redundancy by creating too many New Columns. Otherwise if we use calculated columns to mark each date, we will have these columns.

Author: Yaning Y.

Reviewer: Ula and Kerry

Dynamic rollback of the first n weeks of data.pbix