Forum Discussion
Count Consecutive Days Worked
OK, here I go again (seems when I used the code block it won't post my reply so trying again without)
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_Deckler4 years agoCommunity Champion
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?
- mickjaeger4 years agoHelper I
Greg_Deckler yes a measure indeed,
Scheduled Hours = SUM('scheduled_hours_fact'[roster_scheduled_time])