Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
msj72432
Regular Visitor

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: 

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

 

msj72432_1-1678894040093.png

 

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.

msj72432_0-1678894279270.png

 

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 4
msj72432
Regular Visitor

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. 

 

IdDate CategoryDateEvent TypeEvent Value
5570PlannedApprovalDate03/15/23CompletePlanned
5570ActualExecutionDate03/10/23ExecuteActual
5570LBEPlannedExecutionDate03/17/23LBEPlanned
5570PlannedExecutionDate03/08/23ExecutePlanned
5571PlannedApprovalDate04/06/23CompletePlanned
5571PlannedExecutionDate03/30/23ExecutePlanned
5572ActualExecutionDate02/28/23ExecuteActual
5572PlannedExecutionDate02/13/23ExecutePlanned
5573ActualExecutionDate02/28/23ExecuteActual
5573PlannedExecutionDate03/06/23ExecutePlanned
5574ActualExecutionDate02/24/23ExecuteActual
5574PlannedExecutionDate02/20/23ExecutePlanned
5575ActualExecutionDate02/24/23ExecuteActual
5575PlannedExecutionDate02/20/23ExecutePlanned
5576ActualExecutionDate02/28/23ExecuteActual
5576PlannedExecutionDate02/20/23ExecutePlanned
5577PlannedExecutionDate02/20/23ExecutePlanned
5577ActualExecutionDate02/27/23ExecuteActual
5578PlannedExecutionDate02/20/23ExecutePlanned
5578ActualExecutionDate02/27/23ExecuteActual
5606ActualApprovalDate03/15/23CompleteActual
5606PlannedApprovalDate04/11/23CompletePlanned
5606ActualExecutionDate03/14/23ExecuteActual
5606LBEPlannedExecutionDate03/17/23LBEPlanned
5606PlannedExecutionDate04/10/23ExecutePlanned
5607ActualApprovalDate03/01/23CompleteActual
5607PlannedApprovalDate03/30/23CompletePlanned
5607PlannedExecutionDate03/27/23ExecutePlanned
5608ActualApprovalDate02/07/23CompleteActual
5608PlannedApprovalDate03/30/23CompletePlanned
5608ActualExecutionDate02/05/23ExecuteActual
5608PlannedExecutionDate03/27/23ExecutePlanned
5610ActualApprovalDate03/01/23CompleteActual
5610PlannedApprovalDate03/20/23CompletePlanned
5610LBEPlannedExecutionDate03/17/23LBEPlanned
5610PlannedExecutionDate03/13/23ExecutePlanned
5611ActualApprovalDate03/01/23CompleteActual
5611ActualExecutionDate03/06/23ExecuteActual
5611PlannedExecutionDate03/06/23ExecutePlanned
5612ActualApprovalDate02/08/23CompleteActual
5612ActualExecutionDate02/01/23ExecuteActual
5612PlannedExecutionDate02/20/23ExecutePlanned
5615PlannedExecutionDate03/13/23ExecutePlanned
5616PlannedExecutionDate03/30/23ExecutePlanned
5617ActualExecutionDate03/10/23ExecuteActual
5617LBEPlannedExecutionDate03/10/23LBEPlanned
5617PlannedExecutionDate02/27/23ExecutePlanned
5618PlannedExecutionDate03/03/23ExecutePlanned
5623PlannedExecutionDate03/17/23ExecutePlanned
5624PlannedExecutionDate03/17/23ExecutePlanned
5625PlannedExecutionDate03/17/23ExecutePlanned
5627PlannedExecutionDate03/13/23ExecutePlanned
5628PlannedExecutionDate03/22/23ExecutePlanned
5630PlannedExecutionDate03/17/23ExecutePlanned
5630ActualExecutionDate03/09/23ExecuteActual
5631PlannedExecutionDate03/17/23ExecutePlanned
5632LBEPlannedExecutionDate03/30/23LBEPlanned
5632PlannedExecutionDate03/17/23ExecutePlanned
5633LBEPlannedExecutionDate03/30/23LBEPlanned
5633PlannedExecutionDate02/27/23ExecutePlanned
5634LBEPlannedExecutionDate03/30/23LBEPlanned
5634PlannedExecutionDate03/20/23ExecutePlanned
5635LBEPlannedExecutionDate03/30/23LBEPlanned
5635PlannedExecutionDate03/17/23ExecutePlanned
5636LBEPlannedExecutionDate03/30/23LBEPlanned
5636PlannedExecutionDate03/17/23ExecutePlanned
5638LBEPlannedExecutionDate03/30/23LBEPlanned
5638PlannedExecutionDate03/17/23ExecutePlanned
5639LBEPlannedExecutionDate03/30/23LBEPlanned
5639PlannedExecutionDate02/27/23ExecutePlanned
5640LBEPlannedExecutionDate03/30/23LBEPlanned
5640PlannedExecutionDate02/27/23ExecutePlanned
5641PlannedExecutionDate03/02/23ExecutePlanned
5642PlannedExecutionDate04/03/23ExecutePlanned
5643PlannedExecutionDate03/28/23ExecutePlanned
5644PlannedExecutionDate03/17/23ExecutePlanned
5647ActualExecutionDate03/13/23ExecuteActual
5647PlannedExecutionDate03/17/23ExecutePlanned
5648PlannedExecutionDate04/10/23ExecutePlanned
5649PlannedExecutionDate04/10/23ExecutePlanned
5651PlannedExecutionDate03/03/23ExecutePlanned
5661PlannedApprovalDate03/20/23CompletePlanned
5661PlannedExecutionDate03/21/23ExecutePlanned
5662PlannedApprovalDate03/17/23CompletePlanned
5662LBEPlannedExecutionDate03/13/23LBEPlanned
5662PlannedExecutionDate03/13/23ExecutePlanned
5663PlannedApprovalDate03/17/23CompletePlanned
5663LBEPlannedExecutionDate03/13/23LBEPlanned
5663PlannedExecutionDate03/14/23ExecutePlanned
5664PlannedApprovalDate03/17/23CompletePlanned
5664LBEPlannedExecutionDate03/13/23LBEPlanned
5664PlannedExecutionDate03/16/23ExecutePlanned
5665PlannedApprovalDate03/17/23CompletePlanned
5665LBEPlannedExecutionDate03/13/23LBEPlanned
5665PlannedExecutionDate03/16/23ExecutePlanned
5666PlannedApprovalDate03/30/23CompletePlanned
5666LBEPlannedExecutionDate03/27/23LBEPlanned
5666PlannedExecutionDate03/27/23ExecutePlanned
5667PlannedApprovalDate03/30/23CompletePlanned
5667LBEPlannedExecutionDate03/27/23LBEPlanned
5667PlannedExecutionDate03/17/23ExecutePlanned

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

lbendlin
Super User
Super User

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
Community Support
Community Support

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.