cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

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

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(
)``````

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.

1 ACCEPTED SOLUTION
Super User

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)
)``````

4 REPLIES 4
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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)
)``````

Helper II

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.

Super User

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

...

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

Announcements

Fabric certifications survey

Certification feedback opportunity for the community.

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors