Forum Discussion
Solved
Count Consecutive from Work day
Hi how can I count Consecutive workday from workday as you can see 1 , 2 , 5 , 6 , 7 , 8 from column Workday.
Consecutive workday should be 1,2 , 1,2,3,4
thank you for your help
Hi matus_jun ,
Here is my test for your reference.
Consecutive workday = VAR _minweek = WEEKNUM(MINX(ALL('WorkdayCalendar'[Date]),'WorkdayCalendar'[Date]),2) VAR _interval = DATEDIFF(MINX(ALL('WorkdayCalendar'[Date]),'WorkdayCalendar'[Date]),MAX('WorkdayCalendar'[Date]),DAY)+1 VAR _weekday = WEEKDAY(MAX('WorkdayCalendar'[Date]),1) RETURN IF(WEEKNUM(MAX('WorkdayCalendar'[Date]),1)=_minweek,_interval,_weekday)Best regards,
Mengmeng Li
4 Replies
Make sure that you have a date table, then you can use
Consecutive workdays = VAR ReferenceDate = MAX ( 'Date'[Date] ) VAR AllDates = ALL ( 'Date'[Date] ) VAR WorkDays = CALCULATETABLE ( VALUES ( 'Table'[Workday] ), ALLSELECTED () ) VAR NonWorkDays = EXCEPT ( AllDates, WorkDays ) VAR PreviousNonWorkDay = MAXX ( FILTER ( NonWorkDays, 'Date'[Date] <= ReferenceDate ), 'Date'[Date] ) VAR Result = INT ( ReferenceDate - PreviousNonWorkDay ) RETURN ResultHow can i have date table that I can use with workday I create one but result I got is all 0
Make sure that there is no relationship between date and the workday column, Power BI can create relationships automatically. All you need the date table for in this case is to generate a list of dates which are not work days, for that you need a full calendar.
Hi matus_jun ,
Here is my test for your reference.
Consecutive workday = VAR _minweek = WEEKNUM(MINX(ALL('WorkdayCalendar'[Date]),'WorkdayCalendar'[Date]),2) VAR _interval = DATEDIFF(MINX(ALL('WorkdayCalendar'[Date]),'WorkdayCalendar'[Date]),MAX('WorkdayCalendar'[Date]),DAY)+1 VAR _weekday = WEEKDAY(MAX('WorkdayCalendar'[Date]),1) RETURN IF(WEEKNUM(MAX('WorkdayCalendar'[Date]),1)=_minweek,_interval,_weekday)Best regards,
Mengmeng Li
