Tracking Data over time question

[Anonymous]

Hi, i'm hoping this is an easy one...

I have 2 data sources. one which lists products with date due which doesnt really change (called Table Schedule) . And one which lists product arrival dates which is updated and issued weekly (called Table all Arrivals & date downloaded). I'm pulling each of these weekly (Arrivals) tables into one big table with a query.

Below is an example of the data tables. Please note date format is in UK format.

I'd like to ultimately track what was due each week from the top table based on the date from the other tables, and track what is due each week fom the 2 bottom tables as an example. Which would give the chart below.. What is the right way to go about doing this?

And also to be able to Track down to Product part ID to see, for example, that part A-1 was due some weeks ago but still hadnt arrived as of 08/01/2022.

 

many thanks

J

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Here's my solution.

1.If your Arrivals tables are multiple separate tables, you need to append them into one table.

2.Rename the table to Arrivals, then click Close&Apply.

3.Make sure there's no relationship between Schedule table and Arrivals table.

4.In the Arrivals table, create two calcualted columns, one is WeekYear and  the other is sort. And sort WeekYear column by sort column.

WeekYear = "Week "&WEEKNUM([Date of Data Download])&" "&YEAR([Date of Data Download])

sort = YEAR([Date of Data Download])*100+WEEKNUM([Date of Data Download])

5.Create two measures.

Product Arrived = CALCULATE(COUNT('Arrivals'[Product-Part ID]),ALLEXCEPT(Arrivals,Arrivals[WeekYear]))

Product Due = CALCULATE(COUNT('Schedule'[Product-Part ID]),FILTER('Schedule',[Date Due]<MAX('Arrivals'[Date of Data Download])))

6.Results is as follows. This is to maximize the effect you want. You can filter by the slicer.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Create a common date table, Join one with Due date and join second on arrival date

Create week in date table, use that in Visual and use count measure from other two tables 

Week Number = WEEKNUM([Date],2)

You can also check visual

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA200000675?tab=Overview

[Anonymous]

Thanks for your reply Amitchandak

Ive already got these tables joined to a Products table so when i try to join them via the date table it says i cant as creating a relationship between them would introduce ambiguity between them.

any ideas?