Calculating sum filtering by comparing dates from two different tables

[thibbos]

Hi all,

Thanks for your help upfront. Always a pleasure to see how fast everyone is reacting.

So quickly:

I need to compare the orders from table 1 to the deliveries of table 2.

Table 1: Store - Ordered SKU - QTY Ordered - Date Ordered

Table 2: Store - bought sku - qty bought - Date received

As you can see, there's no singular number (order Number) that would help me match both tables.

What I want to  be able to do:

I want to be able to see the QTY DELIVRED where DATE RECEIVED is higher than DATE ORDERED.

Both tables are linked to a third DATESHEET table.

I tried a calculate with a filter, but filter only lets you make a comparison into the same table (Filter(Datesheet, datesheet(date)>table1(Date ordered) .... something like that.

Question is, how do you calculate and filter somehting if you want to compare dates from different tables.

Thanks a lot

Thibbos

[amitchandak]

@thibbos , You need to have three dimension tables. 

1. Date (Both tables it will join two dates, one active /one inactive. Use userelationship)

2. SKU

3. Store

For the first one refers:

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

For 2-3 refer

https://www.youtube.com/watch?v=Bkf35Roman8

Also read: https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

[thibbos]

Hi Amit,

Thanks for all the links. been through each one fo them but still don't really see how I can do a time comparison between tables in a calculate(sum) context.

Let me try to rephrase:

- I need to sum all sales (from sales table)

- if the date (from sales table) is greater than movement date (from order table)

Thanks