Forum Discussion

TanguyM's avatar
TanguyM
Regular Visitor
4 years ago

Calculate active contract having missing dates

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...
v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
4 years ago

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.