Forum Discussion
Calculating sum filtering by comparing dates from two different tables
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
2 Replies
Anonymous , 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:
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/
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
