Combining two fact tables with different date structures

Hi,

Hopefully this is the correct forum category. I’ve been trying all sorts of different ways to overcome my issue, but I’ve hit a very dark brick wall (maybe it’s the isolation!).

I have 2 separate fact tables, that have prices with very different date structures.

image

The PLATTS table has a date column with unique dates, but the CRU column has a date column with the same date for each week of the month e.g. March 2020 has data for 4 weeks, with the same date 01/03/2020.
If i was reporting CRU on it’s own, i’d be okay, but i am required to take 57% of the CRU value and 43% of another index price from the PLATTS table to create a new price. I’d like to report this new value by week.

I’m really stuck as to how I get this result…do i create a new table, is there a way to create a date field that will allow this type of cross calculation, or am i missing another option completely?
Any help would be much appreciated.
Regards,
Bronwyn

Hi @bgood,

Question. Do both tables hold one weekly price?

If so I noticed that your PLATTS table also holds two Week columns maybe one corresponds to the CRU table? Than you can use that to create a common identifier. Let me try to explain further.
If any of those week columns correspond to your CRU table you’ll have 1 half of a key - the other part of the key you can create in Power Query by going to the PLATTS table, select that unique date column and go to: Add Column/Date/Month/Start of Month.
Now that both tables have a column for Start of Month AND Week number you can look up a single unique date for each week from the PLATTS table and Merge that to your CRU table. Once you have that just go to: Add Column/Date/Week/Start of Week (in both tables).

Make sure your Dates table has the Start of Week column as well and now for every day in your date table you can use the Start of Week column to calculate or lookup the price in PLATTS and CRU.

I hope this is helpful.
If this is your scenario and you need further assistance, just post a small sample. Thanks!

1 Like

Hi @bgood, does this video help?..

Hi @Melissa, thank you for your response.

The week columns that you see in the PLATTS table have been generated by me. I’ve attached a picture of the PLATTS table for you to compare to the CRU table in my initial post.

I have tried to create a week column that can be used to create some kind of relationship between these two tables, but as you can see in my example for March, the CRU table has 4 weeks of data for March, and PLATTS has 5 weeks. The CRU table is created by an external provider, so i cannot collect this data any other way. The PLATTS data i scrape off a website (for which we have a paid subscription for) so this data is available at a bit more granularity.

If i could just get the weeks to line up in each data source, i could work with it. My feeling is that the CRU data is updated on a Thursday each week, and so I think the number of Thursday’s in a month dictates how many weeks of pricing the file will contain for that month.

I have tried creating a week column in the PLATTS table to correspond to this logic, but have failed :frowning:

I hope this provides more context.
Regards,
Bronwyn

Thank you @marcster_uk, but unfortunately this does not help with my particular issue, but I do think the use of TREATAS will be helpful down the line.

Hi Bronwyn,

Can you supply a small sample or mockup file? Then I can test a few things - thanks!

Hi @bgood, we’ve noticed that no response has been received from you since the 16th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

Hi @Melissa I’m sorry for the late response.

I finally figured out a way, and it was very long winded, but i got there in the end.

After realising that the CRU prices were updated on a Thursday, I created a week beginning Thursday column in my dates table, and was able to create the same column in the CRU table, allowing me to make a meaningful relationship between the two.

Here is what i did in pictures:

  1. I created a weekDate column which gave me the first thursday of each month

  1. I then created a ThursdayDate which gave me the correct Thursday date for each week number

  1. I re-created the ThursdayDate column in my dates table

  1. Created a relationship between the Date table and CRU table using the WeekDate

This has allowed me to achieve the outcome I was looking for, even though it felt like a very hard way to get there :slight_smile:

Excellent. :+1: Glad to hear you’ve resolved it.

Just one note. You could achieve the same result using Power Query. That way - if you ever need the same logic in another file - you can just copy these Queries. If that need arises now - you’ll have to remember to also recreate the Calculated Columns because they’re not a part of your Query…

1 Like

It’s great to know that you are making progress with your query @bgood. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

Hi Melissa,

May I request, if you could please share the M Code script with the community for the solution that @bgood implemented?

It would be beneficial to if anyone else runs into similar issue.

Thanks in advance
–Joe

Hi @joedias ,

If you supply the scenario complete with a mockup PBIX and some sample data, I’d be glad to take a look for you. Just open a new thread and @ mention me - thanks

@joedias I actually ended up taking @melissa advise and rewrote my solution in M. I will post my code in here, after the weekend :slight_smile: :grinning:

1 Like

Hi @joedias,

I used the following M code in my fact table (CRU) to achieve the above.

  1. Added a column for the day of week beginning Monday

#“Added Custom” = Table.AddColumn(#“Removed Columns”, “WeekDay”, each Date.DayOfWeek([Date],Day.Monday), Int64.Type)

  1. Added a column to get the date of the Thursday for each date

#“Added Custom1” = Table.AddColumn(#“Added Custom”, “ThursDate”, each if [WeekDay] <= 3 then Date.AddDays([Date], -[WeekDay] +3)
else if [WeekDay] = 4 then Date.AddDays([Date], -[WeekDay] +2)
else if [WeekDay] = 5 then Date.AddDays([Date], -[WeekDay] +0)
else Date.AddDays([Date], [WeekDay] -2),
type datetime)

  1. Then for each date in the fact table i allocated it a thursday date

#“Added Custom2” = Table.AddColumn(#“Changed Type1”, “WeekDate”, each if [WeekNumber] = 1 then [ThursDate]
else Date.AddDays([ThursDate], ([WeekNumber]-1)*7), type datetime)

  1. I completed the same in my PLATTS fact table

  2. I then Merged the PLATTS query into the CRU query and applied the filters i needed

This is quite a specific requirement based on my organisations needs, so not sure it’s worth going into too much detail, but the M code for the week day manipulations are probably helpful.

Regards,
Bronwyn