Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Data Days is here! Join us now for 60+ days of learning, challenges, and connection. Learn more

Reply
seppel123
Frequent Visitor

Calculate Sum of last 7 days for each employee and every date

‎11-15-2024 12:53 AM

Hi all,

 

i am handling a query that includes information about employees and their working times / shift. There are regulatory requirements that in 7 days the working time is not allowed to be higher than 60 hours. This i want to analyze.

To the query: In total there are over 50 employees from which 20-30 work every day. Hence, per year there are around 10.000-15.000 lines. In the query the working time for every day and every employee is listed. See following (shorted) example: 

 

DateNameWorking Time
01.01.2024Employee110,7
01.01.2024Employee210,7
01.01.2024Employee310,7
01.01.2024Employee410,7
01.01.2024Employee97,5
02.01.2024Employee510,7
02.01.2024Employee610,7
02.01.2024Employee110,7
02.01.2024Employee210,7
02.01.2024Employee97,5
03.01.2024Employee710,7
03.01.2024Employee810,7
03.01.2024Employee510,7
03.01.2024Employee610,7
03.01.2024Employee97,5
04.01.2024Employee310,7
04.01.2024Employee45,35
04.01.2024Employee710,7
04.01.2024Employee810,7
05.01.2024Employee110,7
05.01.2024Employee210,7
05.01.2024Employee310,7
05.01.2024Employee410,7
06.01.2024Employee510,7
06.01.2024Employee610,7
06.01.2024Employee110,7
06.01.2024Employee210,7
07.01.2024Employee75,35
07.01.2024Employee810,7
07.01.2024Employee510,7
07.01.2024Employee610,7
08.01.2024Employee310,7
08.01.2024Employee410,7
08.01.2024Employee710,7
08.01.2024Employee85,35

 

I need to add a column now that calculates the sum of working times of that employee over the last 7 days. Result would look like this:

 

DateNameWorking TimeSum7Day
07.01.2024Employee510,742,8
07.01.2024Employee610,742,8
08.01.2024Employee310,732,1
08.01.2024Employee410,726,75
08.01.2024Employee710,737,45
08.01.2024Employee85,3537,45

 

Together with an AI tool i managed to get a working M code. That included an index column (same dates have the same index number): 

 

 

 

    Sorted = Table.Sort(Source, {{"Name", Order.Ascending}, {"Index", Order.Ascending}}),
    7DaySum = Table.AddColumn(Sorted, "Sum7Day", each 
        let
            CurrentIndex = [Index],
            CurrentName = [Name],
            StartIndex = if CurrentIndex - 6 < 1 then 1 else CurrentIndex - 6,
            Last7Rows = Table.SelectRows(Sorted, 
                each [Name] = CurrentName and [Index] >= StartIndex and [Index] <= CurrentIndex),
            Summe = List.Sum(7DaySum[Working Time])
        in
            Summe)

 

 

 

 

The code is very computationally intensive and therefore takes a very long time. Hence, i was hoping there was a better method to achieve this calculation. 

 

Thanks in advance, 

seppel123

 

Labels:
Message 1 of 7
676 Views
0
1 ACCEPTED SOLUTION
Bibiano_Geraldo
Super User
Super User

‎11-15-2024 03:08 AM

Hi @seppel123 ,
I did the logic using DAX, try to use this DAX code:

Measure = 
VAR Nome = SELECTEDVALUE(WorkingTimes[Name])
VAR CurrentDate = MAX(WorkingTimes[Date])
RETURN
CALCULATE(
    SUM(WorkingTimes[Working Time]),
    WorkingTimes[Name] = Nome,
    WorkingTimes[Date] <= CurrentDate &&
    WorkingTimes[Date] > CurrentDate - 7
)

 

 

 

You can achieve the same by the M code bellow:

let
    Source = YourTable,  // Replace "YourTable" with the name of your table
    AddedCustom = Table.AddColumn(Source, "Sum7Day", each 
        let
            CurrentName = [Name],
            CurrentDate = [Date],
            FilteredRows = Table.SelectRows(Source, each 
                [Name] = CurrentName and
                [Date] >= Date.AddDays(CurrentDate, -6) and
                [Date] <= CurrentDate
            ),
            RollingSum = List.Sum(FilteredRows[Working Time])
        in
            RollingSum
    )
in
    AddedCustom

 
See the output bellow:

Bibiano_Geraldo_0-1731669041742.png

 

If this help you, please consider to accept as solution and give a kudo.

 

Thank you

View solution in original post

Message 6 of 7
605 Views
0
6 REPLIES 6
Bibiano_Geraldo
Super User
Super User

‎11-15-2024 03:08 AM

Hi @seppel123 ,
I did the logic using DAX, try to use this DAX code:

Measure = 
VAR Nome = SELECTEDVALUE(WorkingTimes[Name])
VAR CurrentDate = MAX(WorkingTimes[Date])
RETURN
CALCULATE(
    SUM(WorkingTimes[Working Time]),
    WorkingTimes[Name] = Nome,
    WorkingTimes[Date] <= CurrentDate &&
    WorkingTimes[Date] > CurrentDate - 7
)

 

 

 

You can achieve the same by the M code bellow:

let
    Source = YourTable,  // Replace "YourTable" with the name of your table
    AddedCustom = Table.AddColumn(Source, "Sum7Day", each 
        let
            CurrentName = [Name],
            CurrentDate = [Date],
            FilteredRows = Table.SelectRows(Source, each 
                [Name] = CurrentName and
                [Date] >= Date.AddDays(CurrentDate, -6) and
                [Date] <= CurrentDate
            ),
            RollingSum = List.Sum(FilteredRows[Working Time])
        in
            RollingSum
    )
in
    AddedCustom

 
See the output bellow:

Bibiano_Geraldo_0-1731669041742.png

 

If this help you, please consider to accept as solution and give a kudo.

 

Thank you

Message 6 of 7
606 Views
0
seppel123
Frequent Visitor
In response to Bibiano_Geraldo

‎11-15-2024 03:13 AM

Thank you, that works! 

Is it also possible to count how often this measure calculates > 60h for a treemap visulation?

 

Best regards

seppel123

Message 7 of 7
601 Views
0
ryan_mayu
Super User
Super User

‎11-15-2024 01:04 AM

why for last 7 days ,only contains date  07.01 and 08.01?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Message 2 of 7
664 Views
0
seppel123
Frequent Visitor
In response to ryan_mayu

‎11-15-2024 01:07 AM

Hi,

the result is just an example. The shown shorted query has only 8 days, so on day 7 and 8 we can sum over 7 days for sum of the employees. Thats why i shorted it.

 

Best regards

seppel123

Message 3 of 7
653 Views
0
ryan_mayu
Super User
Super User
In response to seppel123

‎11-15-2024 01:16 AM

pls see if this is what you want





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Calculate Sum of last 7 days for each employee and every date.pbix
Message 4 of 7
636 Views
0
seppel123
Frequent Visitor
In response to ryan_mayu

‎11-15-2024 02:42 AM

Hi,

thank you for the help! I need the sum for every date, so i could plot it at the end: 

seppel123_3-1731666707318.png

 

Diagram is from the actual data. Each color is a name. 

With the measure that did not work. Later on, i also wanted to count how often >60h per 7 days happened and visualize via treemap...

 

Maybe the real dataset is needed: 

https://file.io/9mFAetN3VFsx

 

Best regards

seppel123

 

Message 5 of 7
608 Views
0

Helpful resources

Announcements
Fabric Data Days is here Carousel

Fabric Data Days 2026

Don't miss out on Data Days, June 15 through August 7. Learn Fabric, Power BI, SQL, AI and more.

May Power BI Update Carousel

Power BI Monthly Update - May 2026

Check out the May 2026 Power BI update to learn about new features.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

View All