Return value from custom calendar if date is between two dates

[jgdgsf2]

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	Email	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!

[speedramps · Accepted 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

• date
• week ending date
• year
• week/year
• previous week

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

[jgdgsf2]

Hi speedramps,

Thank you very much - all sorted and working with the first solution you offered!