How to Connect Start Date and End Date to Orders Table in Power BI?

[anilpoda]

I have a Dim_Week table and Fact_OrderLines table. Dim_Week table has Start Date and End Date columns, OrderLines table has Order Date column, How can I establish relationship here? What is the importance of Start Date and End Date?

Download Pbix file from below link

Pbix File 

Note: Ignore Date Table which is created by me. Consider Dim_Week table and how can we connect it to Orders table.

[alxdean]

hi @anilpoda , you can not create a relationship on two columns from the same table. So you have two options. you build some funky dax to filter between start and end, or.... you expand the Dim_Week table. what you need to generate in the end has to look something like this:

Date           | Week| Start           | End

02.01.2023 | WK1 |  02.01.2023 | 08.01.2023

03.01.2023 | WK1 |  02.01.2023 | 08.01.2023

04.01.2023 | WK1 |  02.01.2023 | 08.01.2023

05.01.2023 | WK1 |  02.01.2023 | 08.01.2023

06.01.2023 | WK1 |  02.01.2023 | 08.01.2023

07.01.2023 | WK1 |  02.01.2023 | 08.01.2023

08.01.2023 | WK1 |  02.01.2023 | 08.01.2023

09.01.2023 | WK2 |  09.01.2023 | 15.01.2023

10.01.2023 | WK2 |  09.01.2023 | 15.01.2023

etc.

you also have to make a decision how to handle 01.01.2023, as still sits in WK52 from 2022 for some calendars. 

Hope this helps. I won't tell you how to explode the list straight up, just a tip: Have a look at the LIST funktion in Power Query and think about ways to create a "Cross -Join". 

[olgad]

Hi,
Create a key Key FY_Wk like in Dim_Week for Date Table and Order Lines, based on Order Date. Then simply Date-Week Table-Order Sales Table. 

[anilpoda]

Hi Olgad,

I was told by the company there is no need to create a seperate date table. You have to use Dim_Week table. Question here is, there are 2 date columns in Dim_Week table Start Date and End Date. How do I proceed in this situation? How can I establish relation between Dim_Week table and Fact_OrderLines.

Note: There is Fact_OrderHeader with all the order dates. Kindly download the file and propose a solution. I'm stuck with this for over a week.

Pbix File