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

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.