Forum Discussion
Calculate Function to Create a Graph - LBE vs Original
Please let me know if these additional inputs help.
When the data is brought in to PowerBI, multiple line items are generated for each ID (this data set comes from a SharePoint list). A few transformations are run to get to the data set below:
= Table.RenameColumns(#"Unpivoted Columns1",{{"Attribute", "Date Category"}, {"Value", "Date"}})
= Table.AddColumn(#"Renamed Columns1", "Event Type", each if Text.Contains([Date Category], "LBE") then "LBE" else if Text.Contains([Date Category], "Execution") then "Execute" else if Text.Contains([Date Category],"Approval") then "Complete" else null)
To then get to the graph above (planned line), the following formula is used. For the X-Axis, a simple week end date was set up.
So this is where my issue is, if I replace "Event Type" with "LBE", it only shows records that have an LBE date. But, if I leave it as "Execute", it does not consider the LBE. Because of the multiple lines for each record, I have not been able to find a way to consider LBE if it exists but also leave in the original date if LBE doesn't exist.
| Id | Date Category | Date | Event Type | Event Value |
| 5570 | PlannedApprovalDate | 03/15/23 | Complete | Planned |
| 5570 | ActualExecutionDate | 03/10/23 | Execute | Actual |
| 5570 | LBEPlannedExecutionDate | 03/17/23 | LBE | Planned |
| 5570 | PlannedExecutionDate | 03/08/23 | Execute | Planned |
| 5571 | PlannedApprovalDate | 04/06/23 | Complete | Planned |
| 5571 | PlannedExecutionDate | 03/30/23 | Execute | Planned |
| 5572 | ActualExecutionDate | 02/28/23 | Execute | Actual |
| 5572 | PlannedExecutionDate | 02/13/23 | Execute | Planned |
| 5573 | ActualExecutionDate | 02/28/23 | Execute | Actual |
| 5573 | PlannedExecutionDate | 03/06/23 | Execute | Planned |
| 5574 | ActualExecutionDate | 02/24/23 | Execute | Actual |
| 5574 | PlannedExecutionDate | 02/20/23 | Execute | Planned |
| 5575 | ActualExecutionDate | 02/24/23 | Execute | Actual |
| 5575 | PlannedExecutionDate | 02/20/23 | Execute | Planned |
| 5576 | ActualExecutionDate | 02/28/23 | Execute | Actual |
| 5576 | PlannedExecutionDate | 02/20/23 | Execute | Planned |
| 5577 | PlannedExecutionDate | 02/20/23 | Execute | Planned |
| 5577 | ActualExecutionDate | 02/27/23 | Execute | Actual |
| 5578 | PlannedExecutionDate | 02/20/23 | Execute | Planned |
| 5578 | ActualExecutionDate | 02/27/23 | Execute | Actual |
| 5606 | ActualApprovalDate | 03/15/23 | Complete | Actual |
| 5606 | PlannedApprovalDate | 04/11/23 | Complete | Planned |
| 5606 | ActualExecutionDate | 03/14/23 | Execute | Actual |
| 5606 | LBEPlannedExecutionDate | 03/17/23 | LBE | Planned |
| 5606 | PlannedExecutionDate | 04/10/23 | Execute | Planned |
| 5607 | ActualApprovalDate | 03/01/23 | Complete | Actual |
| 5607 | PlannedApprovalDate | 03/30/23 | Complete | Planned |
| 5607 | PlannedExecutionDate | 03/27/23 | Execute | Planned |
| 5608 | ActualApprovalDate | 02/07/23 | Complete | Actual |
| 5608 | PlannedApprovalDate | 03/30/23 | Complete | Planned |
| 5608 | ActualExecutionDate | 02/05/23 | Execute | Actual |
| 5608 | PlannedExecutionDate | 03/27/23 | Execute | Planned |
| 5610 | ActualApprovalDate | 03/01/23 | Complete | Actual |
| 5610 | PlannedApprovalDate | 03/20/23 | Complete | Planned |
| 5610 | LBEPlannedExecutionDate | 03/17/23 | LBE | Planned |
| 5610 | PlannedExecutionDate | 03/13/23 | Execute | Planned |
| 5611 | ActualApprovalDate | 03/01/23 | Complete | Actual |
| 5611 | ActualExecutionDate | 03/06/23 | Execute | Actual |
| 5611 | PlannedExecutionDate | 03/06/23 | Execute | Planned |
| 5612 | ActualApprovalDate | 02/08/23 | Complete | Actual |
| 5612 | ActualExecutionDate | 02/01/23 | Execute | Actual |
| 5612 | PlannedExecutionDate | 02/20/23 | Execute | Planned |
| 5615 | PlannedExecutionDate | 03/13/23 | Execute | Planned |
| 5616 | PlannedExecutionDate | 03/30/23 | Execute | Planned |
| 5617 | ActualExecutionDate | 03/10/23 | Execute | Actual |
| 5617 | LBEPlannedExecutionDate | 03/10/23 | LBE | Planned |
| 5617 | PlannedExecutionDate | 02/27/23 | Execute | Planned |
| 5618 | PlannedExecutionDate | 03/03/23 | Execute | Planned |
| 5623 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
| 5624 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
| 5625 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
| 5627 | PlannedExecutionDate | 03/13/23 | Execute | Planned |
| 5628 | PlannedExecutionDate | 03/22/23 | Execute | Planned |
| 5630 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
| 5630 | ActualExecutionDate | 03/09/23 | Execute | Actual |
| 5631 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
| 5632 | LBEPlannedExecutionDate | 03/30/23 | LBE | Planned |
| 5632 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
| 5633 | LBEPlannedExecutionDate | 03/30/23 | LBE | Planned |
| 5633 | PlannedExecutionDate | 02/27/23 | Execute | Planned |
| 5634 | LBEPlannedExecutionDate | 03/30/23 | LBE | Planned |
| 5634 | PlannedExecutionDate | 03/20/23 | Execute | Planned |
| 5635 | LBEPlannedExecutionDate | 03/30/23 | LBE | Planned |
| 5635 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
| 5636 | LBEPlannedExecutionDate | 03/30/23 | LBE | Planned |
| 5636 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
| 5638 | LBEPlannedExecutionDate | 03/30/23 | LBE | Planned |
| 5638 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
| 5639 | LBEPlannedExecutionDate | 03/30/23 | LBE | Planned |
| 5639 | PlannedExecutionDate | 02/27/23 | Execute | Planned |
| 5640 | LBEPlannedExecutionDate | 03/30/23 | LBE | Planned |
| 5640 | PlannedExecutionDate | 02/27/23 | Execute | Planned |
| 5641 | PlannedExecutionDate | 03/02/23 | Execute | Planned |
| 5642 | PlannedExecutionDate | 04/03/23 | Execute | Planned |
| 5643 | PlannedExecutionDate | 03/28/23 | Execute | Planned |
| 5644 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
| 5647 | ActualExecutionDate | 03/13/23 | Execute | Actual |
| 5647 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
| 5648 | PlannedExecutionDate | 04/10/23 | Execute | Planned |
| 5649 | PlannedExecutionDate | 04/10/23 | Execute | Planned |
| 5651 | PlannedExecutionDate | 03/03/23 | Execute | Planned |
| 5661 | PlannedApprovalDate | 03/20/23 | Complete | Planned |
| 5661 | PlannedExecutionDate | 03/21/23 | Execute | Planned |
| 5662 | PlannedApprovalDate | 03/17/23 | Complete | Planned |
| 5662 | LBEPlannedExecutionDate | 03/13/23 | LBE | Planned |
| 5662 | PlannedExecutionDate | 03/13/23 | Execute | Planned |
| 5663 | PlannedApprovalDate | 03/17/23 | Complete | Planned |
| 5663 | LBEPlannedExecutionDate | 03/13/23 | LBE | Planned |
| 5663 | PlannedExecutionDate | 03/14/23 | Execute | Planned |
| 5664 | PlannedApprovalDate | 03/17/23 | Complete | Planned |
| 5664 | LBEPlannedExecutionDate | 03/13/23 | LBE | Planned |
| 5664 | PlannedExecutionDate | 03/16/23 | Execute | Planned |
| 5665 | PlannedApprovalDate | 03/17/23 | Complete | Planned |
| 5665 | LBEPlannedExecutionDate | 03/13/23 | LBE | Planned |
| 5665 | PlannedExecutionDate | 03/16/23 | Execute | Planned |
| 5666 | PlannedApprovalDate | 03/30/23 | Complete | Planned |
| 5666 | LBEPlannedExecutionDate | 03/27/23 | LBE | Planned |
| 5666 | PlannedExecutionDate | 03/27/23 | Execute | Planned |
| 5667 | PlannedApprovalDate | 03/30/23 | Complete | Planned |
| 5667 | LBEPlannedExecutionDate | 03/27/23 | LBE | Planned |
| 5667 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
not sure I fully understand the ask. how about this version?