How to get a history of valid products given a start and end date

[DorienM]

Scenario:

Our company leases software to other companies. All software licenses we issue come with a start and an end date. Given a 'Products Licensed' table like the one shown below how can I make a line graph that shows how many copies of each product were licensed on any given day?

Data model:

'Products Licensed' 10000+ rows, start date goes back to 1/12015, max end date is 12/31/2099

Product Name	Quantity	Start Date	End Date
Product A	11	6/10/2019	7/10/2019
Product B	24	3/31/2019	10/13/2019
Product B	13	4/5/2019	6/5/2019
Product C	31	8/22/2019	12/14/2019

There is also a 'Date' table, it is amrked as the date table and has a relationship to both start and end date, start date is active.

What I have tried:
I have given various expressions a chance but the one that I think is closest to what I need is this

SumOfValidHistoric = 
    var _firstDate = FIRSTDATE('Date'[Date])
    var _lastDate = LASTDATE('Date'[Date])
return
    CALCULATE(
        SUM('Products Licensed'[Quantity]),
        'Products Licensed'[StartDate] <= _lastDate,
        'Products Licensed'[EndDate] >= _firstDate
    )

I plot this on a line graph with 'Date'[Date] on my X axis.

Expected outcome:

I expect to see somethone along the lines of this (assume the sample data above is far more extensive than what I have shown)

Issues:

The expression  shown above returns blanks when in the same visual as the dates, this results in a blank ling graph or, when converted to a table visual, a list of dates with nothing next to it.

As always any help is appreciated.

[Ashish_Mathur]

Hi,

Another way to solve this question is to create one row for each date in the table and then just build 1 relationship from the Date column to the Date column of the Calendar Table.  I suspect this solution too will pose performance problems but let me know if you are interested in getting this solution. 

[sevenhills · Accepted solution]

Looks like you are doing similar to current / active employees scenario by Department. You are trying as by Product Name (kind of Department) and Current Licenses (as Active Employees count)

Say, you have set the relationships as below, try this measure

Current Licenses = 

CALCULATE(
  sumx(
    Filter ( 'Table', 'Table'[Start Date] <= Max('Date'[Date]) 
                       && 'Table'[End Date] > MAx('Date'[Date]))
    , 'Table'[Quantity])

  , CROSSFILTER('Table'[Start Date],'Date'[Date],None)
)

[DorienM]

That did the trick! Thank you so much.

Performance is pretty slow with a lot of records but I think thats to be expected in this calculation. I'll include a performance note to my users to try and keep it to 2 years of records at a time.

[sevenhills]

Now that you have the solution, please check these links or you can search online, and tune to your needs to improve the performance

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

https://finance-bi.com/blog/power-bi-employee-count-by-month/ 

https://www.youtube.com/watch?v=uWpwZMfP5ns

... 

Or post some huge (unidentified data) where you are seeing the performance, I can take a look.