Forum Discussion
Issue with Formulas - When Multiple Rows Similar Rows Exist
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!
Super UserHI,
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)?
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-anbandariCommunity Support
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!
