Last 5 Weeks

[Xandman]

Hi

I'm looking for a way to display PW, 2PW, 3PW, 4PW and 5PW. I have a date table with weeknumbers in it. Currently this is how my table looks like:

Instead of 28, 29 etc. I would like it to be PW so users can understand it better.  I tried to create a measure to get the previous week, but when I put it on the matrix, its blank. It's also blank in a normal table.

I found a link to a guide here and applied it to mine:

Count Previous Week =

VAR CurrentWeek = SELECTEDVALUE(DateTable[ISO Weeknumber])

VAR CurrentYear = SELECTEDVALUE(DateTable[ISO Year])

VAR MaxWeekNumber = CALCULATE( MAX( DateTable[ISO Weeknumber] ), FILTER( ALL( DateTable ), DateTable[ISO Year] = CurrentYear - 1 ) )

RETURN

SUMX(

FILTER( ALL(DateTable),

if(CurrentWeek = 1,

DateTable[ISO Weeknumber] = MaxWeekNumber && DateTable[ISO Year] = CurrentYear -1,

DateTable[ISO Weeknumber] = CurrentWeek -1 && DateTable[ISO Year]) ),

[TotalWork])

then TOTALWORK is:

TotalWork = CALCULATE(

DISTINCTCOUNT(CompletedWork[W #]))

[v-rzhou-msft · Accepted solution]

Hi @Xandman 

From your statement, I know you want to show Flags like PW,PW1 for previous months instead of only weeknum.

From your code, I know you have two tables, date and completework tables. 

Completework table is your data table. Here I build a sample.

I think you need to build a date table and add a custom weeknum column and year column in it by dax. If you use weeknum function 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 ,year and rank.

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" )
)

Calculate 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])

YearWeekRank = RANKX('Date','Date'[WeekYear]*100+'Date'[ISO 8601 WeekNum],,ASC,Dense)

PW Flag =
VAR _CurrentRank =
    CALCULATE (
        MAX ( 'Date'[YearWeekRank] ),
        FILTER ( 'Date', 'Date'[Date] = TODAY () )
    )
RETURN
    SWITCH (
        TRUE (),
        'Date'[YearWeekRank] = _CurrentRank - 1, "PW",
        'Date'[YearWeekRank] = _CurrentRank - 2, "PW2",
        'Date'[YearWeekRank] = _CurrentRank - 3, "PW3",
        'Date'[YearWeekRank] = _CurrentRank - 4, "PW4",
        'Date'[YearWeekRank] = _CurrentRank - 5, "PW5",
        BLANK ()
    )

 Date table will look as below.

Then relate completework table and this date table by date columns.

Build measures as below.

TotalWork = CALCULATE(DISTINCTCOUNT(CompletedWork[W#]),FILTER('Date','Date'[PW Flag]<>BLANK()))

Count Previous Week = SUMX('Date',[TotalWork])

Build a matrix and 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.

[Xandman]

Thank you, this has worked as intended. I was wondering how I could make the labelling dynamic based on the date selected? The label is currently fixed on the current week.

If I selected a different date, is it possible to see the previous weeks of that date?

[selimovd]

Hey @Xandman ,

you could add a calculated column in the date table and check there if the week is PW, PW2, etc. and then put the corresponding value. This column you can then use in the visual and it will also update every time you refresh the file.

Would that work for you?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic