Count Consecutive Days Worked

[mickjaeger]

Hi Everyone,

I don't get to work in DAX often enough and am still a newbie when it comes to creating advanced measures with DAX.  

I have seen many, many proposed solutions out there for this scenario but most are very complex and super confusing.  I did find one solution from Alberto Ferrari at SQLBI that is super graceful but the problem is they count days with zero sales and I need for example to count days with sales greater than zero.  I tried getting feedback from Alberto via YouTube but so far no response.

This is the link to the YouTube video (Alberto does a super job explaining this as he builds it.):  https://www.youtube.com/watch?v=GR9ROCQVyLk

The working DAX code looks like this (credit to Alberto Ferrari):

When I used this with my data I got a sample report like this (my data is showing the hours an employee is scheduled to work on a given day, this result set is for one employee only):

DaysWorked below is really showing DaysOff, this is the crux, need to flip to actually count the consecutive days worked instead.

This DAX code works beautifully, just as advertized!  It's a bit slow to process but it's compact and functional.

Can anyone figure out how to modify this DAX to count the days with data instead of the days without data?

Thank you all in advance!!!

Mick

[mickjaeger]

OK, here I go again (seems when I used the code block it won't post my reply so trying again without)

@Greg_Deckler ,

Your solution looks like it might work but currently '__LastBlank' variable is not returning a date so the result is wrong.  Not sure if you can help me review the code as I implemented your suggestion.

I tested the __CurrentDate and __CurrentHours variables and those are returning correct values.  

DaysWorked2 =

VAR __CurrentDate = MAX('date_dimension'[date])

VAR __CurrentHours = 'scheduled_hours_fact'[Scheduled Hours]

VAR __LastBlank = MAXX(FILTER(ALL('scheduled_hours_fact') ,'scheduled_hours_fact'[roster_date] <= __CurrentDate && [Scheduled Hours] = BLANK()) ,'scheduled_hours_fact'[roster_date])

VAR Result = (__CurrentDate - __LastBlank) * 1 RETURN Result

Is this enough info?  

Thanks,

Mick

PS I did see your request for more info about my implementation of Alberto's solution, figured we'd start with your idea but can shift to this other one if you want. 

[Greg_Deckler]

@mickjaeger Need to confirm a couple things. You are creating a measure correct? [Scheduled Hours] is a measure? If [Schedule Hours] is a measure, what is the formula?

[mickjaeger]

@Greg_Deckler yes a measure indeed, 

Scheduled Hours = SUM('scheduled_hours_fact'[roster_scheduled_time])

[mickjaeger]

Gosh this page doesn't like me today, trying and trying and trying to post a reply but it won't take.  Testing this with text only.

[mickjaeger]

@Greg_Deckler thank you for the responses, I don't have time to look at this today.  I will look and respond tomorrow.  Thanks again.

[Greg_Deckler]

@mickjaeger This would have been my solution:

DaysWith02 = 
    VAR __CurrentDate = MAX('Date'[date])
    VAR __CurrentHours = SUM('Hours'[roster_scheduled_time])
    VAR __LastBlank = MAXX(FILTER(ALL('Hours'),'Hours'[date]<=__CurrentDate && [roster_scheduled_time] = BLANK()),'Hours'[date])
RETURN (__CurrentDate - __LastBlank) * 1.

[Greg_Deckler]

@mickjaeger First, odd, when I attempt to apply that code I get back all 0's. Can you share the actual implementation of your code?