Count Consecutive from Work day

[matus_jun]

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

[Anonymous · Accepted solution]

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

[johnt75]

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
    Result

[matus_jun]

How can i have date table that I can use with workday I create one but result I got is all 0

[johnt75]

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.