cancel
Showing results 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

Helper I

## Power BI : Dynamic past 365 days 95th Percentile

Hi, I have a scenario where I need to calculate 95th percentile for dynamic past 365 days for each row. Please refer below snip, where user will select the date range and based on that selection, all dates will be listed one by one.  As you can see against each date, Lowest balance for them is displayed.

What I want is, to compare 95th percentile for past 365 days against the Lowest balance for that day and display whether 95th percentile is Above or Below than the lowest balance of that day.

So far, I have tried below formula but it returns the same value as of Lowest Balance.

95%tile_Measure =
var _end_date = MAX(date)
var _start_date = _start_date - 365
RETURN CALCULATE(PERCENTILE.INC(low_balance, 0.95), FILTER(Fact_Table, date >= _start_date && date <= _end_date))

1 ACCEPTED SOLUTION
Helper I

Got it, I was missing the ALL clause in my DAX.

95%tile_Measure =
var _end_date = MAX(date)
var _start_date = _start_date - 365
RETURN CALCULATE(PERCENTILE.INC(low_balance, 0.95), ALL(date), date >= _start_date && date <= _end_date)

Helper I

Got it, I was missing the ALL clause in my DAX.

95%tile_Measure =
var _end_date = MAX(date)
var _start_date = _start_date - 365
RETURN CALCULATE(PERCENTILE.INC(low_balance, 0.95), ALL(date), date >= _start_date && date <= _end_date)

Announcements

#### 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.