Issue with Formulas - When Multiple Rows Similar Rows Exist

[agraca75]

Hi - I was wondering if somebody could assist trying to diagnose how I can fix an issue I am having with some data.

I have a file file from a system that has types of absences based on safety incidents.  Due to the nature of the data I need to figure out the Lost Work Days / Modified Work Days based on Fiscal Period using the Start Date and End Date.

For example to determine the number of days for March I am using the following formula to determine the week days minus holidays between 2 dates.  

if [START_DATE] > #date(2025,3,29) then 0

else if [END_DATE] < #date(2025,2,23) then 0

else if [START_DATE] < #date(2025,2,23) and [END_DATE] > #date(2025,3,29) then fnNETWORKDAYS(#date(2025,2,23), #date(2025,3,29), Holidays[Date])

else if [START_DATE] >= #date(2025,2,23) and [END_DATE] > #date(2025,2,22) then fnNETWORKDAYS([START_DATE], #date(2025,3,29), Holidays[Date])

else if [START_DATE] < #date(2025,2,23) and [END_DATE] < #date(2025,3,29) then fnNETWORKDAYS(#date(2025,2,23), [END_DATE], Holidays[Date])

else if [START_DATE] >= #date(2025,2,23) and [END_DATE] < #date(2025,3,29) then fnNETWORKDAYS([START_DATE], [END_DATE], Holidays[Date])

else 0

On the whole this is working correctly except in cases where multiple lines exist for the same incident in the same fiscal period.  I tried to add an Index by Group then Merged this with the Incident Number & Case No to create unique IDs - Same Result.

Here is an example - in this data I have 2 lines with a break in between for the same incident.  Minus the weekend there is about 10 days incurred here.

But after doing the formula the calculated formula is way higher.  

Any help in diagnosing this would be appreciated.  

[v-anbandari]

Hi @agraca75  ,

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you.

[ralf_anton]

Hab mir das jetzt nochmal genauer abgesehen. Dein Problem liegt ja in der Anzeige der Pivottable.

Die Ursache dafür liegt in der Anordnung der Felder für die Zeilen und Spalten.

Entweder, Du verwendest die Datumsangaben in den Zeilen:

oder wenn Du schon die Monate allein verwendest, dann schaffe eine Möglichkeit, die Monatssummen auch tageweise aufsplitten zu lassen:

Denn so, wie Du es aktuell darstellst, erhältst Du die gesamte Monatssumme. Und die ist ja im Grunde genommen korrekt.

Fazit: Passe die Aufteilung und Darstellung Deiner Pivottabelle an.

[ralf_anton]

---

@agraca75 wrote:

Das führt zu einer größeren Frage, ...

...

Anstatt die Daten in den Code festzukodieren, ist es also möglich, eine Tabelle mit den Daten zu referenzieren?

****************************************************************************************************************

Wenn das die Frage ist, dann kann die mit ja beantwortet werden.

In meinem Post vom Donnerstag (23.4.) referenziere ich im 7. Schritt (im übersetzen Code der Schritt AddHolidaycheck) die Feiertagstabelle. Im Schritt 3 (im übersetzen Code jetzt AddDateListColumn) erstelle ich eine Liste aller Tage zwischen den Start und Enddaten.

Du kannst also sowohl die Feiertagstabelle als auch die Tageslisten ganz konkret referenzieren, ohne jeden einzelnen Tag hart codieren zu müssen.

Damit Du das besser nachvollziehen kannst, stelle ich Dir mal den Code mit angepassten, englischen Schrittbezeichnungen ein:

Hier der Code zum direkten kopieren:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    ChangeDateToNumber = Table.TransformColumnTypes(Quelle,{{"Start", Int64.Type}, {"Ende", Int64.Type}}),
    AddDateListColumn = Table.AddColumn(ChangeDateToNumber, "Datum", each {[Start]..[Ende]}),
    ExpandDateListColumn = Table.ExpandListColumn(AddDateListColumn, "Datum"),
    ChangeDatetype = Table.TransformColumnTypes(ExpandDateListColumn,{{"Datum", type date}, {"Ende", type date}, {"Start", type date}}),
    AddWeekday = Table.AddColumn(ChangeDatetype, "Weekday", each Date.DayOfWeekName([Datum])),
    AddHolidaycheck = Table.AddColumn(AddWeekday, "istFeiertag", each List.Contains(tblHolidays[Datum],[Datum])),
    DeleteAllWeekendRrows = Table.SelectRows(AddHolidaycheck, each ([Weekday] <> "Samstag" and [Weekday] <> "Sonntag")),
    DeleteHolidayRows = Table.SelectRows(DeleteAllWeekendRrows, each ([istFeiertag] = false)),
    CountGroupedRows = Table.Group(DeleteHolidayRows, {"Start", "Ende", "Code"}, {{"Difference", each Table.RowCount(_), Int64.Type}})
in
    CountGroupedRows

Die deutschen Wochenendbezeichnungen im Schritt DeleteAllWeekendRrows

...each ([Weekday] <> "Samstag" and [Weekday] <> "Sonntag"))

musst Du bei Dir manuell anpassen.

Beachte bitte, meine Beispielquelltabelle (die blaue) hat nur 3 Spalten. Die Überschriften beider Tabellen müssen, wenn Du sie nachstellen willst, natürlich mit denen vom Bild identisch sein oder falls Du eigene verwendest, im Code angepasst werden.

Das Ergebnis der Abfrage tblSolution siehst Du im PQ Editor.

Link zur Datei 

---

[v-anbandari]

Hi @agraca75 ,

Could you please confirm if your issue has been resolved using the suggested approach? This will help other community members facing similar scenarios.
 

Thank you for being part of the Microsoft Fabric Community.

[v-anbandari]

Hi @agraca75   ,

The issue is due to working days being calculated per row, which causes double counting when the same incident has multiple rows in the same period.

I reproduced your scenario with smaple data based on your requirement:

To fix this, you can follow these steps:

• Combine all rows for the same incident instead of treating them separately
• Work at the date level (expand each Start–End range into individual dates)
• Remove weekends (and holidays if needed)
• Make sure the same date is not repeated for the same incident (remove duplicates)
• Finally, count the remaining dates per incident

  This way, each day is counted only once and the total will be correct.

  
  Output: I have attached the screenshots below showing the expected result
   
  

  

  

  Thanks.

[ralf_anton]

Hallo,

falls ich Dich richtig verstanden habe, willst Du die Tagesdifferenz jeder Zeile abzüglich der Feiertage ermitteln.

Im Beispiel habe ich nur eine rudimentäre Feiertagstabelle, die Du anpassen müsstest. Die Wochenenden hab ich ebenfalls abgezogen. 

Hier der M-Code:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Start", Int64.Type}, {"Ende", Int64.Type}}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Geänderter Typ", "Datum", each {[Start]..[Ende]}),
    #"Erweiterte Benutzerdefiniert" = Table.ExpandListColumn(#"Hinzugefügte benutzerdefinierte Spalte", "Datum"),
    #"Geänderter Typ1" = Table.TransformColumnTypes(#"Erweiterte Benutzerdefiniert",{{"Datum", type date}, {"Ende", type date}, {"Start", type date}}),
    #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Geänderter Typ1", "Weekday", each Date.DayOfWeekName([Datum])),
    #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte1", "istFeiertag", each List.Contains(tblFeiertage[Datum],[Datum])),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Hinzugefügte benutzerdefinierte Spalte2", each ([Weekday] <> "Samstag" and [Weekday] <> "Sonntag")),
    #"Gefilterte Zeilen1" = Table.SelectRows(#"Gefilterte Zeilen", each ([istFeiertag] = false)),
    #"Gruppierte Zeilen" = Table.Group(#"Gefilterte Zeilen1", {"Start", "Ende", "Code"}, {{"Anzahl", each Table.RowCount(_), Int64.Type}})
in
    #"Gruppierte Zeilen"

[Juan-Power-bi]

HI,

The inflated number is almost certainly a double-counting issue — when two rows exist for the same incident in the same period, your formula runs independently on each row and the results get summed up in the visual, so you're adding days that overlap.
The fix is to handle this at the incident level before calculating days. Instead of computing days per row, group by incident first, then calculate the total working days across all date ranges for that incident combined. In Power Query you can do this by grouping on Incident Number + Case No, collecting all the Start/End date pairs into a list, and then passing the merged ranges to your fnNETWORKDAYS function — being careful to merge any overlapping or adjacent ranges before counting.
Can you share a bit more about the structure? Specifically — do the two rows for the same incident ever have overlapping date ranges, or are they always consecutive with a gap (like in your example Plese)? 

[agraca75]

Hi

Took me a bit to review the data and my code.

When I looked at the data I found that the system where the data exists was allowing entries to occur for the same period for a single incident.  So we manually corrected those data points and working to have those issues rectified.  

The next issue I found was some of the logic to determine the days allocated to each fiscal month was incorrect.  The code for that was manually corrected.  I found some errors with the dates for the fiscal months and some errors in the logic (<, >, >=, etc..).

This leads to a bigger question and I am not sure if there is an answer to this.  The dates in the screen grab are tied to our fiscal calendar and there is a lot of repetion in the dates so many opportunities for errors which did occur when I reviewed the code.

So instead of hard coding the dates into the code is it possible to reference a table with the dates.  So January will look at the From and To dates in the table to determine the fiscal month and the same with February and so on.  

[v-anbandari · Accepted solution]

Hi @agraca75   ,

Thanks for the update.

To avoid hardcoding and reduce errors, you can use a reference table with From and To dates for each fiscal month. Then, map your data against this table by matching each date to the corresponding From-To range to determine the fiscal month dynamically.

This approach is easier to maintain, as you only need to update the table when there are changes in the fiscal calendar.

Thank you!