Consecutive days in date range

[EWBWEBB]

Hiya

There are a multitude of posts on this and I've got some way with the SQLBI video on youtube but keep getting stuck with my specific issue. 

I need to report on sickness within the business, both longterm (lts) and short term (sts).

To classify Sickness I use the following logic:

LTS = Sick for 28 or more consecutive days AND sick on the last date in the period.

STS = Sick for less than 28 days

Further, if someone starts their period of absence in a previous period, but do not meet the LTS critera for the previous period, (but the absence is part of the same consecutive string) then the days in the previous period should be marked as STS and the days in the period where it is triggered should be marked as LTS.

AND

Once someone is LTS they should remain as LTS, for example if I trigger the LTS condition in the current period but return before the end of the next then those days should also be LTS not STS even though the absence has ended before the end of the period. 

For tagging each day I would ideally like this as columns as opposed to a measure, both for how many consecutive days and the LTS/STS.

I will want to do further work with this information which will be easier to deal with if I can see it in the table.

**you will note from the calendar table below I report on two seperate periods of data (helpful I know). I'm happy that this is in seperate columns. I will use the Calendar as a filter and use measures for the reporting figures.

I hope this makes sense, particularly the LTS/STS conditions but please feel free to ask for further clarification. 

Also I'm not particularly fussy if this is done in Queery or as a calculated column. Conscious of the potential size of the table as this grows YOY and will need to be mindful of the size.

The data will be coming from a SQL Database, as an import. (our in house SQL knowledge is poor so keen that this is done somewhere I can be in control of it) 
The report table which shows every day of the year for every person in the business (circa. 3000 people - so annually just over a million rows, minimum)

The sickness data table is laid out as follows

The calendar is

The report is linked as follows on the date key

[v-zhangti · Accepted solution]

Hi, @EWBWEBB 

You can try the following methods.

Consecutive days = 
CALCULATE(COUNT('Table'[Date]),FILTER('Table',[ID]=EARLIER('Table'[ID])))

Status = IF([Consecutive days]>=28,"LTS","STS")

Because of the small amount of sample data provided, there are some special cases that we have not been able to encounter yet. You can refer to the following link to upload PBIX example files, where sensitive information can be removed in advance.

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,

Community Support Team _Charlotte

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