Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
EWBWEBB
Helper II
Helper II

Consecutive days in date range

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

 

EWBWEBB_0-1653398483978.png

 

The calendar is

EWBWEBB_1-1653398548000.png

 

 

The report is linked as follows on the date key

 

EWBWEBB_2-1653398568949.png

 

 

 

 

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

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")

vzhangti_0-1653633111493.png

 

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.

View solution in original post

1 REPLY 1
v-zhangti
Community Support
Community Support

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")

vzhangti_0-1653633111493.png

 

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.