CODEWITHSUNDEEP
powerbidax
Aaron
Aaron

Reputation: 331

Dax : COUNT Entries per week in a row

I have an HR Report that shows me if an employee has worked on the weekend. This is the structure:

Name    Number  Date    Type    Value
Anton   100 14.02.2021  WeekendWork 1
Anton   100 20.02.2021  WeekendWork 1
Anton   100 27.02.2021  WeekendWork 1
Anton   100 06.03.2021  WeekendWork 1
Kevin   101 01.03.2021  RegWork     5
Julian  102 01.03.2021  RegWork     7
Julian  102 06.03.2021  WeekendWork 1
Julian  102 07.03.2021  WeekendWork 1

As you can see we have the "Type" = WeekendWork. If an employee has worked on a weekend this entry will be shown with the "Value" = 1

Now I would like to determine, how many weekends the employee has worked in the previous 5 weeks. Just the last 5 weeks. It doesn't matter if it was a Sunday and/ or Saturday.

How would you do that using DAX?

Upvotes: 1

Views: 657

Answers (1)

Przemyslaw Remin
Przemyslaw Remin

Reputation: 6950

I will give you some clue on how to push it forward.

It is best practise to have a Calendar table for time intelligence measures. Search for that. Connect Calendar table to your Employee table. Add to your Calendar a column WeekendEndDate, like here below.

Calendar = 
VAR BaseCalendar =
    CALENDAR (
        DATE ( 2021,  1,  1 ),
        DATE ( 2021, 12, 31 )
    )
RETURN
    GENERATE (
        BaseCalendar,

    VAR VarDates = [Date]
    VAR VarDay = WEEKDAY ( VarDates )

    RETURN
        ROW (
            "day"     , VarDay,
            "WeekendEndDate" , // This returns the last date of the weekend. 
                SWITCH( VarDay, 
                7, VarDates+1, // Saturday 
                1, VarDates,   // Sunday
                BLANK() )      // Any other day
        )
)

If it is a Weekend date, no matter if Saturday or Sunday, the column WeekendEndDate returns Sunday's date. You would want to count cases of WeekendWork type per that date. Or at least check if this number is greater than zero.

You will have to use time intelligence functions like DATESINPERIOD on Calendar[date] to grab the last 5 weeks.

Code to recreate the sample Employee table:

= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcswryc9T0lEKSk0Pzy/KBrKMDIwMdQ0MdY0NlWJ1EArCU1OzU/NSUBQZ6RqaoChCN8VI18gCrwJjoE0ErQEqMiNGkTlYkVd+Bi41cE9B1WA6F+ofnPJGBO2AexmHGXDP4JYH2hELAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Type = _t, Date = _t])

enter image description here

Upvotes: 1

Related Questions