Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
So that was not as easy as I thought - but at least I think it works as an example now.
Agian thank you for the interest. Excited to see if anyone has some ideas thats aplicable.
I think opening the file gives the best idea as to whats the issue, but below there is an image of both the count of days (T1) and the solution (T3)
link to file from google drive
I started tinkering with this by calculating a summary table of persons and dates.
Summary =
FILTER (
ADDCOLUMNS (
SUMMARIZECOLUMNS (
Person[Person_ID],
D_Datotabel[Dato],
"Hours", CALCULATE ( SUM ( data[hours] ) ) + 0
),
"ConsecuctiveDays",
VAR FirstShift = CALCULATE ( MIN ( data[Shift_start] ), REMOVEFILTERS ( D_Datotabel ) )
VAR DateRange = DATESBETWEEN ( D_Datotabel[Dato], FirstShift, D_Datotabel[Dato] - 1 )
VAR AllShifts =
CALCULATETABLE (
VALUES ( data[Shift_start] ),
ALL ( D_Datotabel[Dato] ),
data[hours] > 0
)
VAR LastDayOff = MAXX ( EXCEPT ( DateRange, AllShifts ), D_Datotabel[Dato] )
VAR RunStart = COALESCE ( LastDayOff, FirstShift - 1 )
RETURN
IF ( [Hours] > 0, D_Datotabel[Dato] - RunStart )
),
[ConsecuctiveDays] > 0
)
You can turn the ConsecutiveDays part into a measure but there's still some work to be done. I'm attaching a .pbix from where I left off.
Thanks a bunch for your efforts.
Looking forward to test- fully booked for meetings all day plus 3 deadlines (seriously what do they expect) so might be a stretch if I manage today.
Only thing is that so far when I have work in the table with adding columns it crahshes - I figured the dataset was too large as it has somewhere between 15 and 25 mill lrows and a few more columns than was included in the sample file.
But still excited too try it out.
With a dataset that big, you might want to do some pre-aggregation at the query stage if possible.
I 'll try to upload a sample set later today. My motherboard died so I am on a temp machine trying to recreate studd - including this. Thanks for the interest. br kasper
Hi @REGHnoob ,
I'm not quite sure if I understand your needs accurately.
Sample data without sensitive information and expected output would help tremendously.
Please see this post regarding:
How to get your question answered quickly
How-to-provide-sample-data-in-the-Power-BI-Forum
Hope it helps,
Community Support Team _ Caitlyn
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
This sounds challenging. Can you specify what the final result should look like? I think I get the general idea but how you plan to show it in a visual can make a significant difference.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |