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: 

Learn from the best! Meet the four finalists headed to the FINALS of the Power BI Dataviz World Championships! Register now

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
547 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
476 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
477 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
472 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
535 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
524 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
507 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
479 Views
0

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

Share feedback directly with Fabric product managers, participate in targeted research studies and influence the Fabric roadmap.

February Power BI Update Carousel

Power BI Monthly Update - February 2026

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

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