Day of the week and leave type

[AthonyALS]

Hi All,

So I do have an absence tracker that I constantly use that uses start date and end date to track number of days employees are sick. is there a way to use the Key Infuencers visualization to show that SUU was more common on Mondays or fridays for instance

[v-yangliu-msft · Accepted solution]

Hi  @AthonyALS ,

Here are the steps you can follow：

1. In power query – Add Column – Custom  Column .

Duration.Days([END DATE] - [START DATE])

2. Enter data -- Create the following table.

This is derived from [Subtraction] of the Table, next to the Index number, how many rows will be generated.

3. Home  -- Merge Queries -- Merge Queries as new

4. Click the extension icon and select [Index].

Result:

5. Create calculated column.

Date =
var _min=MINX(FILTER(ALL(Merge1),'Merge1'[ID]=EARLIER('Merge1'[ID])),[START DATE])
return
IF(
    'Merge1'[Flag.Index]=MINX(FILTER(ALL(Merge1),'Merge1'[ID]=EARLIER('Merge1'[ID])),[Flag.Index]),
    _min+1,
    _min +[Flag.Index])-1

Day =
FORMAT('Merge1'[Date],"dddd"
)

Column =
COUNTX(
    FILTER(ALL(Merge1),
    'Merge1'[Day]=EARLIER('Merge1'[Day])),[Day])

6. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly