Forum Discussion

mickjaeger's avatar
mickjaeger
Helper I
4 years ago

Count Consecutive Days Worked

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 sc...
Need Help
mickjaeger's avatar
mickjaeger
Helper I
4 years ago

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's avatar
Greg_Deckler
Community Champion
4 years ago

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's avatar
    mickjaeger
    Helper I
    4 years ago

    Greg_Deckler yes a measure indeed, 

     

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