Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 =
then TOTALWORK is:
Solved! Go to 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.
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.
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?
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?
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |