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: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! 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
471 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
400 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
401 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
396 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
459 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
448 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
431 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
403 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All