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!!!
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.
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?
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.
@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 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.
@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?
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.