cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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 3
parry2k
Super User III
Super User III

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Anonymous
Not applicable

Sorry it's unclear in my statement. 

I hope to create a line chart like this:

Capture.PNG

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!

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors