Looking for a Better Way of Showing Contracts Active by Date

[Shelley]

Hi All, I have a situation where my report and visual are doing exactly what I want them to do, but I am trying to find another way that will be less of a burden on my data model and be faster for the user(s) of my report.

I want to look at services used over the life of a contract, and show the data as such. That is, a customer may pass the start date of their contract, but three months may go by before they consume any of their entitlement, so the graph should show zero for those months and those months only. For example, here is a contract that begins in December 2019. I do NOT want this where it shows all dates because the contract didn't begin until Dec 2019:

I only want where it shows the date the contract began as below. 

The blue dotted line is what they are entitled to, while the purple line is their usage.

To accomplish this, and since I have contract start date and contract end date, I created a table called Contract Active Dates in Power BI with DAX. This creates a table that has a date and contract number for every single date a contract number is active:

Contract Active Dates =

SELECTCOLUMNS (

GENERATE (

'Entitlement Summary Table',

FILTER (

ALLNOBLANKROW ( 'RA_Daily_Calendar'),

AND (

'RA_Daily_Calendar'[Date] >= 'Entitlement Summary Table'[Contract Start Date],

'RA_Daily_Calendar'[Date] <= 'Entitlement Summary Table'[Contract End Date]))),

"Date", 'RA_Daily_Calendar'[Date],

"Contract Number", 'Entitlement Summary Table'[Contract Number])

I also created a Flag as follows to designate which contracts are active NOW.

Flag: Active Main Line Contract Entitlement =

IF(TODAY() >= 'Entitlement Summary Table'[Contract Start Date] && 'Entitlement Summary Table'[Header Life Cycle Status] = "Released" && 'Entitlement Summary Table'[Line Item Life Cycle Status] = "Active - Released", 1, 0)

Then on the visual, I added filters:

- Flag: Active Main Line Contract Entitlement is 1  (For Active Contract)

- Count of 'Active Contract Dates'[Contract Number] > 0

It works great in displaying exactly what I want, but it seems to slow performance and also generates that large table. So, is there another way to do this without generating the large Contract Active Dates table? Or, is there a way to generate this table using M Query that will enable the model to run faster when the user is using the Power BI report?

[amitchandak]

@Shelley , refer to my blog around a similar topic if that can help

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

or video :https://youtu.be/e6Y-l_JtCq4