Efficient Method to Dynamically Pull Data Based on Calculated Percentage in Power BI

[JennyVill]

Hi Power BI Community,

I have a SQL data source that contains the most recent data and the last 5 historical runs of data, each specified with a run date.

Here's what I need to achieve in Power BI:

1. Pull in the most recent run of data if a calculated percentage is within 5% of the last calculated percentage.
2. If the calculated percentage exceeds 5%, pull in the run date prior to the most recent one.

Could anyone suggest the most efficient way to implement this logic in Power BI? Any guidance on  Power Query steps, or other methods would be greatly appreciated!

From the information I have seen, it looks like I can do this w/ a parameter using power query but I'm still very confused on how to start. Do I need to create a seperate table with the run date and % so that I can use that as a criteria? 

Thank you in advance for your help!

[Anonymous · Accepted solution]

Hi @JennyVill 

Thank you very much lbendlin for your prompt reply.

For your question, here is the method I provided:

Here's some dummy data

Load your data. Make sure your table contains the run date and percentage columns.

“Table”

Open the advanced editor and write the M code:

let
    Source = ...,
    YourTable = Table.TransformColumnTypes(Source,{{"RunDate", type date}, {"Percentage", type number}}),
  
    // Sort by RunDate descending
    SortedData = Table.Sort(YourTable, {{"RunDate", Order.Descending}}),

    // Add an index column to identify the order of each run
    IndexedData = Table.AddIndexColumn(SortedData, "Index", 0, 1, Int64.Type),

    AddPercentageDiff = Table.AddColumn(IndexedData, "PercentageDiff", each if [Index] = 0 then null else  Number.Abs(IndexedData{[Index]-1}[Percentage] - [Percentage]) * 100),

    AddCustomColumn = Table.AddColumn(AddPercentageDiff, "SelectedRun", each if [Index] = 0 or ([PercentageDiff] <= 5 ) then [RunDate] else IndexedData{[Index]-1}[RunDate]),

    // Filter based on custom columns
    FilteredData = Table.SelectRows(AddCustomColumn, each [SelectedRun] <> null),
    // Select the columns you want to keep
    RemoveColumns = Table.SelectColumns(FilteredData, {"RunDate", "Percentage", "SelectedRun"})

in
    RemoveColumns

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JennyVill]

Thank you so much! I will try this method and let you know 🙂

[lbendlin]

You have two options

1. Implement your entire logic in SQL and present the result to Power Query as a view or SP

2. Ingest all the snapshots into Power Query and then throw away what you don't need.

Option 1 is the most efficient way.

[JennyVill]

Thank you so much for the reply! We're going to see if the logic can be implemented in SQL first and see if that works 🙂