CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Athos
Athos

Reputation: 43

Calendar Table For Time Scale Power BI

I need to create a schedulle with that pattern enter image description here

into a dinamic table, like this

enter image description here

what i tryed :

date = FILTER(CALENDARAUTO(12),[DATE] >=2022)

isDayOff = 

var iniDayOff1 = DATE(2022,12,5)
var iniDayOff2 = DATE(2022,12,8)

return IF( WEEKNUM([DATE])+WEEKDAY(IniDayOff1)-2 ||  WEEKNUM([DATE])+WEEKDAY(IniDayOff2)-2 = WEEKDAY([DATE]), "X", )

My problem is when WEEKNUM([DATE])+WEEKDAY(IniDayOff1)-2 is bigger then 7, i need that formula to reset, because when dayoff is saturday (WEEKDAY 7), the next dayoff is sunday (WEEKDAY 1)

Upvotes: 0

Views: 83

Answers (1)

Peter
Peter

Reputation: 12325

Use this formula instead:

isDayOff = 
VAR iniDayOff1 = DATE(2022, 12, 5)
VAR iniDayOff2 = DATE(2022, 12, 8)
RETURN
    IF(
        MOD(DATEDIFF(IniDayOff1, 'date'[Date], DAY), 8) = 0
        || MOD(DATEDIFF(IniDayOff2, 'date'[Date], DAY), 8) = 0,
        "X",
        BLANK()
    )

enter image description here

Upvotes: 1

Related Questions