Forum Discussion
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 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
2 Replies
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
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.
