Forum Discussion
Create line chart using filters based on another table
Hi guys, here is my situation:
I have two tables
1. date
01/01/2019
01/02/2019
01/03/2019
...
03/31/2019
2. company eff_date exp_date amount
abc 01/01/2019 01/07/2019 100
abc 01/08/2019 01/28/2019 110
abc 01/29/2019 12/31/9999 120
xyz 01/01/2019 02/04/2019 200
xyz 02/05/2019 12/31/9999 230
....
def 03/12/2019 12/31/9999 300
I want to build a line chart to see how each company's inventory moving throughout the time.
The first table is I created to connect to second table as filter. Ideally I can use the first table as filter to see how is the inventory moving in a specific period of time
The second table is fact table, showing at how many items the company have at a specific time. eff_date is the date that the inventory record refreshed and exp_date is 1 day before next refresh. If exp_date is 12/31/9999, that means the record is up-to-date.
Any idea on how to create the calculated measure?
Thanks so much!
3 Replies
- parry2kSuper User
Anonymous what is the actual question? What end result you are looking for?
- AnonymousNot applicable
Sorry it's unclear in my statement.
I hope to create a line chart like this:
The y-axis is amount of inventory and x-axis is time. The point is that I have to create a measure to get the correct amount at the given time point. eg. at any given time T, the correct amount should be sum of amount with filter eff_date <= T & exp_date > T
I am stuck in creating such a measure.
Thanks!
- parry2kSuper User
Anonymous can you share sample data in excel file thru one drive/google drive?