Forum Discussion

samdep's avatar
samdep
Advocate II
5 years ago
Solved

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

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:

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

5 Replies

    • samdep's avatar
      samdep
      Advocate II
      5 years ago

      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's avatar
    Greg_Deckler
    Community Champion
    5 years ago

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

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

    Then just filter on Column = 0

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      samdep
      Advocate II
      5 years ago

      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!