Between dates calculation

[mlsoulliere]

Hi there - struggling with one last issue. I have a table with start and end dates. If the service is active, then the end date is null. I am looking to see how many people had an open service between dates. If their end service date is null then it should be counted everyday from start date to today. I have created a table called AutoCalendar and used the following in the measure.

My old data looks great as they all have start and end dates. However my more recent data is off as its not counting people without an end date populated. Help!!!

Measure42 = Calculate(countrows('table'),

filter('table', 'table'[ServiceDateStart]< min('CalendarAuto'[Date]) && 'table'[ServiceDateEnd] > max('CalendarAuto'[Date])))

[Anonymous]

Hi @mlsoulliere ,

Depending on your needs, your measure used to work fine in your data model, but the current situation is that recent data is off as its not counting people without an end date populated after the data refresh, if I understand you correctly, you can try to modify your measure following this example (I assume your end Date is a situation where a blank value occurs, causing the measure to not be calculated correctly)

_Measure =Var_Enddate=if(Max(ServiceDateEnd) = Blank(),today(),Max(ServiceDateEnd))ReturnCalculate(countrows('table'),filter('table', 'table'[ServiceDateStart]< min('CalendarAuto'[Date]) && 'table'[ServiceDateEnd] > max('CalendarAuto'[Date])))

You can modify the measure to suit your dataset situation.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[GilbertQ]

Hi @mlsoulliere 

That is as you say because there is no end date. What you should do in your data is to replace the NULL values with the current date to ensure that it is not null.

When using the auto date it will use the MAX or last date in the data, and because your new data is NULL it will only use the previous max date.

[mlsoulliere]

@GilbertQ thank you! Any ideas on how to add the current date? I've figured how to change a date that I choose for the null values but nothing about how to dynamicly add in the current date to today's values. Thanks again!

[GilbertQ · Accepted solution]

Hi @mlsoulliere 

You could always do this in Power Query where you are importing the data and where there are null values to replace it with the current date, which in Power Query would be DateTime.LocalNow()