Forum Discussion
Tracking Data over time question
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
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.
3 Replies
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
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.
