itil

ITIL Reporting

excel

ITIL 4

excel

Power BI

excel

Excel Formula | Macro | Script

Power BI: put weekend if Friday and Monday have values

It is easy to put weekend beside the corresponding dates but it is another task if I want to put it only if Friday and Monday have values so I have this table:

power bi

To include the weekend in the “support”, I will use this formula:

IF(ISBLANK('table'[argument1]),IF(WEEKDAY('table'[argument2],2)>=6,"WE"),'table'[argument1])

power bi

NOTE:

  • Change table and argument by yours
  • The WEEKDAY 2 means that the week starts on Monday (1) so 6 is Saturday

The point is that I want the weekend to appear only if Friday and Monday have values so if one of them is empty, the weekend should not show. For that, first I will need to identify each group with an ID:

var groupid=IF(NOT(ISBLANK('table'[argument3])),CALCULATE(COUNTBLANK('table'[argument3]),FILTER('table',
'table'[argument2]<=EARLIER('table'[argument2]))))
return
IF(ISBLANK(groupid),IF(NOT(ISBLANK('table'[argument3])),0),groupid)

power bi

Once done, I will use this formula to get what I want:

var countgroup=IF(NOT(ISBLANK('table'[argument4])),COUNTX(FILTER('table',EARLIER('table'[argument4])='table'
[argument4]),'table'[argument3]))
var countwe=IF([argument3]="we",COUNTX(FILTER('table',EARLIER('table'[argument4])='table'[argument4] && EARLIER('table'[argument3])='table'[argument3]),'table'[argument3]))
return
IF(countgroup>3,[argument3],IF(countgroup-countwe>1,[support]))

power bi

Let’s go a little further, imagine that I change my mind and I also want the weekend to appear when Friday has a value. In this case, first, I need to know the day ID using this formula:

IF(NOT(ISBLANK('table'[argument3])),WEEKDAY('table'[argument2],2))

power bi

Then with this formula to get what I need:

var countgroup=IF(NOT(ISBLANK('table'[argument4])),COUNTX(FILTER('table',EARLIER('table'[argument4])='table'
[argument4]),'table'[argument3]))
var countwe=IF([argument3]="we",COUNTX(FILTER('table',EARLIER('table'[argument4])='table'[argument4] && EARLIER('table'[argument3])='table'[argument3]),'table'[argument3]))
var combinewe=IF(NOT(ISBLANK('table'[argument3])),CONCATENATEX(FILTER('table','table'[argument4]=EARLIER
('table'[argument4])),'table'[Column 4],""))
return
IF(countgroup>3,[argument3],IF(combinewe<>"167",[argument3],IF(countgroup-countwe>1,[argument1])))

power bi

NOTE: instead of Friday, I want Monday, just change 167 by 567 (column 6)

power bi

Interesting Topics