Calculate active contract having missing dates

[TanguyM]

Hello, 

In my dataset, I have only the date where contracts change state from active to inactive or inacitve to active.
The day with no change, there a no row for the contract.

My problem is that I must count the number of active contract every day, like in the image below.

How can I do that in DAX ?
Thanks

[v-yalanwu-msft]

Hi, @TanguyM ;

You could create a new table and a measure as follow:

Newtable = CALENDAR(MIN('Table'[date]),MAX('Table'[date]))

Measure = 
CALCULATE(SUM('Table'[active]),FILTER('Table',[date]<=MAX('Newtable'[Date])))
-CALCULATE(COUNT('Table'[contract]),FILTER('Table',[date]<=MAX('Newtable'[Date])&&[active]=0))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@TanguyM , Create a new column like

End Date = minx(filter(Table, [Contract] =earlier([Contract]) && [Date] > earlier([Date])),[Date]) -1

and then use one of the follwing approch to get adat for all dates

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

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...