Forum Discussion

samdep's avatar
samdep
Icon for Advocate II rankAdvocate 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 Mon...
  • 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.