Hi, I am very new to Power BI but have been learning quickly until I got into this issue.
Please be gentle on me as I am still new and may not know some of the basics.
Background:
I have around 60,000 records in a table that I pull using many SQL joins. As the filters for my visuals are complicated, I created custom columns for filters using and/or conditions. Let's say for visual 1, if all the conditions are satisfied then [Custom_Column_1] will be "True" else "False".
It works fine until here.
Problem:
I have three columns in the main table after using Custom_Filter_1 = "True"
- ID,
- IDType,
- SubmittedDate
Also, I have Calendar table with columns -
- Date,
- DateNext,
- IsWeekDay,
- IsHoliday,
- IsToday,
- IsTomorrow
In the main data, ID Type has Mod and Remod options.
Mod has 1 Day of SLA and Remod has 2 days of SLA. These tasks need to be completed before SLA.
For both the ID Types, I need to check if the IDs are in SLA or out of SLA, even if one ID is out of SLA, then it should be "No". Then, I need to count the number of IDs due today and tomorrow based on SLA considering workdays (Calendar table has [IsWeekDay] and [IsHoliday]).
I get this data when Custom_Filter_1 is added to the visual level filter:
| ID | ID Type | SubmittedDate |
| 1258 | Mod | 11/4/2021 |
| 1217 | Remod | 11/4/2021 |
| 1499 | Remod | 11/4/2021 |
| 1418 | Remod | 11/5/2021 |
| 1351 | Mod | 11/4/2021 |
| 1333 | Mod | 11/5/2021 |
| 1269 | Mod | 11/5/2021 |
| 1318 | Mod | 11/5/2021 |
| 1146 | Mod | 11/5/2021 |
| 1117 | Mod | 11/5/2021 |
| 1404 | Remod | 11/5/2021 |
| 1206 | Remod | 11/5/2021 |
| 1439 | Mod | 11/5/2021 |
| 1155 | Remod | 11/5/2021 |
| 1197 | Mod | 11/8/2021 |
| 1338 | Mod | 11/8/2021 |
| 1198 | Remod | 11/8/2021 |
| 1146 | Remod | 11/8/2021 |
| 1168 | Remod | 11/8/2021 |
| 1126 | Remod | 11/8/2021 |
| 1289 | Remod | 11/9/2021 |
| 1355 | Mod | 11/9/2021 |
| 1016 | Mod | 11/9/2021 |
Calander Table:
| Date | DateNext | IsWeekDay | IsHoliday | IsToday | IsTomorrow |
| 11/1/2021 | 11/2/2021 | 1 | 0 | FALSE | FALSE |
| 11/2/2021 | 11/3/2021 | 1 | 0 | FALSE | FALSE |
| 11/3/2021 | 11/4/2021 | 1 | 0 | FALSE | FALSE |
| 11/4/2021 | 11/5/2021 | 1 | 0 | FALSE | FALSE |
| 11/5/2021 | 11/6/2021 | 1 | 0 | FALSE | FALSE |
| 11/6/2021 | 11/7/2021 | 0 | 0 | FALSE | FALSE |
| 11/7/2021 | 11/8/2021 | 0 | 0 | FALSE | FALSE |
| 11/8/2021 | 11/9/2021 | 1 | 0 | TRUE | FALSE |
| 11/9/2021 | 11/10/2021 | 1 | 0 | FALSE | TRUE |
| 11/10/2021 | 11/11/2021 | 1 | 0 | FALSE | FALSE |
Required Output
| Task | SLA | In SLA (Y/N) | Due Today | Total Due Today | Due Tomorrow | Total Due Tomorrow |
| Mod | 1 Day | No | 2 | 10 | 2 | 12 |
| Remod | 2 Days | Yes | 4 | 11 | 1 | 11 |
- If [IsWeekDay] = 0 or if [IsHoliday] = 1, then it should be counted as working day and it shouldn't be counted in SLA days.
- SLA is fixed. 1 days for Mod and 2 days for Remod.
- For Mod, SLA is 1 day. The task has to be completed before SubmitedDate+1 day. If all the ID Numbers are completed on or before SubmittedDate +1 then "Yes", else "No". The tasks on 11/4/2021 were not completed by 11/5/2021. So it is not in SLA.
- For Remod, the SLA is 2 days. If no ID number exceeds 2 days from SubmittedDate, then it is in SLA.
The Tasks 11/4/2021 are in SLA because 6 and 7 were not working days. So, 11/4/2021 tasks can be completed by 11/8/2021. - Due Today = Count of IDs that are pending today. i.e. 11/8/2021
- Total Due Today = Total count of IDs that are pending until today. i.e. on or before 11/08/2021
I really appreciate your help.
