Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DorienM
Helper II
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 NameQuantityStart DateEnd Date
Product A116/10/20197/10/2019
Product B243/31/201910/13/2019
Product B134/5/20196/5/2019
Product C318/22/201912/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)

paintdotnet_zx9Sdxkhxj.png

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
sevenhills
Super User
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

sevenhills_1-1654899340230.png

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

 

sevenhills_2-1654899717763.png

 

sevenhills_3-1654899742803.png

 

 

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
sevenhills
Super User
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

sevenhills_1-1654899340230.png

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

 

sevenhills_2-1654899717763.png

 

sevenhills_3-1654899742803.png

 

 

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.

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.
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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