DOW Filter Mon, Tue, Wed... Each on Its Own Visual (Current Week)

[samdep]

Hi Community!

I am using stacked barcharts to show how different personnel are spending their workday (based on the time they log). My goal is to display 5 dynamic stacked barcharts - one for Monday, one for Tuesday, etc. in the current week, so that users can see the breakdown of their time over the course of a given week.

Is it possible to overlay a relative date filter or some sort of DAX measure that shows on the Monday barcharts, the time data for Monday, 8/9, until this week ends -- and then, dynamically update to Monday, 8/16, and so on?

Looking to accomplish something similar to the blurry image below:

[AlexisOlson · Accepted solution]

You might be able to use a relative date slicer for this too using this calendar week.

[Anonymous · Accepted solution]

Hi @samdep 

I think you need to build calendar and add a custom weeknum column and year column in it. If you use weeknum directly, you will get wrong weeknum at the end of the previous year and the beginning of the next year.

For example: 

In my Sample I let week start on Sunday.

2020/12/31,2021/01/01,2021/01/02 are in the same weeknum, but weeknum function will give you 53 in 2020 and 1 in 2021. It's wrong result.

Firstly build a calendar table by dax and add calculated columns in it to get correct weeknum and year.

Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2019, 01, 01 ), DATE ( 2021, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "WeekNum", WEEKNUM ( [Date], 1 ),
    "WeekDay", WEEKDAY ( [Date], 1 ),
    "MonthName", FORMAT ( [Date], "MMMM" ),
    "DayName", FORMAT ( [Date], "DDDD" )
)

Calculated columns:

ISO 8601 WeekNum = 
VAR _COUNT0 =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        FILTER (
            'Date',
            'Date'[Year] = EARLIER ( 'Date'[Year] )
                && 'Date'[WeekNum] - 1 = 0
        )
    )
VAR _BASENUM1 =
    IF ( _COUNT0 < 7, 'Date'[WeekNum] - 1, 'Date'[WeekNum] )
VAR _ISO_8601_WeekNum =
    IF (
        WEEKDAY ( DATE ( 'Date'[Year] - 1, 01, 01 ) ) <> 1
            && 'Date'[Year] = 'Date'[Year]
            && _BASENUM1 = 0,
        WEEKNUM ( DATE ( MIN ( 'Date'[Year] ), 12, 31 ), 1 ) - 1,
        _BASENUM1
    )
RETURN
    _ISO_8601_WeekNum

WeekYear = 
VAR _COUNT0 =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        FILTER (
            'Date',
            'Date'[Year] = EARLIER ( 'Date'[Year] )
                && 'Date'[WeekNum] - 1 = 0
        )
    )
VAR _BASENUM1 =
    IF ( _COUNT0 < 7, 'Date'[WeekNum] - 1, 'Date'[WeekNum] )
RETURN
IF(_BASENUM1 = 0,'Date'[Year] -1,'Date'[Year])

Then you can relate this date table with your Data table by date columns. 

 Add a Flag column in your Data table. 

Flag =
VAR _TodayWeeKNum =
    CALCULATE (
        MAX ( 'Date'[WeekNum] ),
        FILTER ( 'Date', 'Date'[Date] = TODAY () )
    )
VAR _TodayWeeKYear =
    CALCULATE (
        MAX ( 'Date'[WeekYear] ),
        FILTER ( 'Date', 'Date'[Date] = TODAY () )
    )
RETURN
    IF (
        RELATED ( 'Date'[WeekNum] ) = _TodayWeeKNum
            && RELATED ( 'Date'[WeekYear] ) = _TodayWeeKYear,
        1,
        0
    )

My Date Table:

Add Flag column into Page Level Filter Field and set it to show items when value equal to 1.

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[samdep]

Thank you so much, @Anonymous! This was very helpful and I ended up doing something similar to your suggestion. I have a calendar table, so I just created a conditional column that assigned 1-7 to each respective day of the week. I then used the Relative Date Slicer for in current week/in previous calendar week and overlaid the weeknum conditional column that I had built (Monday = 1, etc.). Your suggestion helped immensely - thank you!

[AlexisOlson · Accepted solution]

You might be able to use a relative date slicer for this too using this calendar week.

[samdep]

Thank you for this post! For anyone who stumbles on this and is looking for an answer/idea here, I ended up assigning weeknum to each day of the week in my calendar table via a conditional column - and then, I used the relative date slicer (in current week) and overlaid that weeknum column (Mondays = 1, Tuesdays = 2, etc.) as a filter and it worked perfectly. Thanks again!

[Greg_Deckler]

@samdep If I understand correctly, you can use a calculated column like:

Column = WEEKNUM(TODAY()) - WEEKNUM([Date])

Then just filter on Column = 0