Forum Discussion
Calculate Function to Create a Graph - LBE vs Original
Hello all!
I inherited the graph below which uses the calculate function to create a planned execution graph (Test Exeuction Progress - Bottom Left Graph). The light blue line is our weekly planned scripts for testing which uses "Planned Execution Date" to create the line. However, the team I work with is now looking to consider both "Planned Execution Date" or an "LBE Planned Execution Date" if populated. This data is sourced from a SharePoint list where these values are two separate columns.
Code:
The prior owner of this basically created multiple line items for each record which feeds into the formula above. I was able to update the query to create a line item for LBE Planned Execution. However, I am only able to use LBE or the original planned date to create the graph.
Is there a way to add logic to the function to consider the original planned date or the LBE planned date where it is populated?
4 Replies
Hi , Anonymous
According to your description, you need to analyze the planned date or the LBE planned date .
If this , i think you can try to use the field parameter as a dimension for the x-axis.
For more information, you can refer to :
Let report readers use field parameters to change visuals (preview) - Power BI | Microsoft LearnFields parameters in Power BI - SQLBI
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Read about COALESCE()
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Please 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.
TC Planned Execute Cumulative = CALCULATE(COUNTA('Test Execution Tracker'[Date]), FILTER(ALLSELECTED('Test Execution Tracker'),'Test Execution Tracker'[Event Value]="Planned" && 'Test Execution Tracker'[Event Type]="Execute" &&'Test Execution Tracker'[Date]<= MAX('Test Execution Tracker'[Date])))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?
