Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

  • Anonymous what is the actual question? What end result you are looking for?

    • Anonymous's avatar
      Anonymous
      Not 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!

      • parry2k's avatar
        parry2k
        Super User

        Anonymous can you share sample data in excel file thru one drive/google drive?