Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone
I need to compare the numbers of samples arriving at a lab by day - and compare 2019 to 2020. But need the days of the week to line up -
DayNoOfYear =
DATEDIFF ( DATE ( YEAR ( 'Table'[Date] ), 1, 1 ), 'Table'[Date], DAY ) + 1
that formula is all good, but I need day 1 of 2020 (a wednesday) to have the number 2 against it so that it matches with the first day Wednesday in 2019, which is a 2 (day 1 of 2019 is a Tuesday).
Is it possible to modify that formula so that if year = 2020 I can make it be +2 at the end instead of +1 in my date table?
Hope all are well
Claire
Perhaps something like:
DATEDIFF ( DATE ( YEAR ( 'Table'[Date] ), 1, 1 ), 'Table'[Date], DAY ) + 1 + WEEKDAY('Table'[Date]) - WEEKDAY(DATE(YEAR('Table'[Date])-1,1,1))
Would likely need refinement.
Hi Greg, thanks for your reply! I added the formula (day num of year V2) and its done something very curious! am trying to pull it apart to understand why its doing what its doing, as feel like this could be a solution !
This is probably a pretty hack solution, but I just did this:
User | Count |
---|---|
57 | |
21 | |
19 | |
19 | |
16 |
User | Count |
---|---|
85 | |
80 | |
52 | |
37 | |
22 |