Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I have an issue that I'm sure is very simple to solve but so far haven't been able to crack!
Firstly, I have a custom calendar table that looks like this:
WeekNumber | WeekEndsOn | Year | WeekYear | PreviousWeek |
1 | 08-Aug-20 | 2020 | 01/2020 | 01-Aug-20 |
2 | 15-Aug-20 | 2020 | 02/2020 | 08-Aug-20 |
3 | 22-Aug-20 | 2020 | 03/2020 | 15-Aug-20 |
4 | 29-Aug-20 | 2020 | 04/2020 | 22-Aug-20 |
And so on and so on for the full 52 weeks.
I have a separate data set like the below:
ID | Completion time | Name | Employee Name | Location | WeekYear | |
53 | 29/09/2021 10:47 | anonymous | ||||
292 | 03/03/2022 15:14 | anonymous | ||||
112 | 05/11/2021 13:57 | anonymous | ||||
125 | 12/11/2021 09:01 | anonymous |
All I want to do is get the WeekYear from my custom Calendar table based on the Completion Time column - if it falls between xx-xx-xxxx and xx-xx-xxxx then the WeekYear is xx/xxxx.
Is there an easy way of achieving this? Thanks in advance!
Solved! Go to Solution.
HI jgdgsf2
It is really easy, you just need to have the same key on both tables
eg date to date
or week to week
not date to week !
Option1 (date to date) - best option !
Create a proper calendar table with arceord for every date (eg 01/01/2020 to 31/12/2023) with
Then create a 1:M relationship form calandar[date] to facttable[competion date].
You can now get the weekyear
watch this video ...
https://www.youtube.com/watch?v=BtYn1hfdSAM
Option 2 (week to week) :-
In Power Query add an extra colum to your fact table
Date.EndOfWeek([completion date], day of week number)
you can then build a relation and get your WeekYear
Please click thumbs up and Accept as Solution
HI jgdgsf2
It is really easy, you just need to have the same key on both tables
eg date to date
or week to week
not date to week !
Option1 (date to date) - best option !
Create a proper calendar table with arceord for every date (eg 01/01/2020 to 31/12/2023) with
Then create a 1:M relationship form calandar[date] to facttable[competion date].
You can now get the weekyear
watch this video ...
https://www.youtube.com/watch?v=BtYn1hfdSAM
Option 2 (week to week) :-
In Power Query add an extra colum to your fact table
Date.EndOfWeek([completion date], day of week number)
you can then build a relation and get your WeekYear
Please click thumbs up and Accept as Solution
Hi speedramps,
Thank you very much - all sorted and working with the first solution you offered!
User | Count |
---|---|
97 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
96 | |
95 | |
64 | |
57 |