Calculate Function to Create a Graph - LBE vs Original

[msj72432]

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: 

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])))

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? 

[msj72432]

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. 

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

[lbendlin]

not sure I fully understand the ask. how about this version?

[lbendlin]

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-...
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/1447523

[v-yueyunzh-msft]

Hi , @msj72432 

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 Learn

Fields 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