DAX - Rolling 12 months and percentile based on user's defined parameter

[ar13]

Hi,

Below is an example of data I have: 

Date	Indicator	Level
2018-12-03	ABC	16.39
2018-11-30	ABC	18.07
2018-11-29	ABC	18.79
2018-11-28	ABC	18.49
2018-11-27	ABC	19.02
2018-11-26	ABC	18.9
2018-11-23	ABC	21.52

• The dates are business dates, the data goes back several years, and there are multiple indicators within the same table.
• I created a what-if parameter where the user should be able to pick the percentile they want to apply. If it matters, the parameter goes from 0 to 1 by increments of 0.05.
• There is a slicer for the dates on the report.

What I am trying to accomplish is the following:

• The user should be able to see rolling 12 months percentile values for each date and the evolution of the percentile value based on the user' selection.

I tried to creating a column in the data table but got the following messsage: Expressiosn that yield variant data-tupe cannot be used to define calculated columns.

My question is: is that the best way to do it or are there better alternatives

Thanks,

AR

[SQLMonger]

You should be able to use a calculated measure to get what you are looking for.  There is a bit of work you need to do first though, like including your own Calendar table, linked to your data table. This is pretty much a prerequisite if you want to leverage time based statistical functions over a data set.  

The best resource I can put you on is https://www.daxpatterns.com/statistical-patterns/  They have a complete set of statistical measure, and include downlaodable Excel workbooks with working examples.

If you are trying to get a Percentile, as opposed to a percentage, there are PERCENTILE functions in DAX. Details on the functions can be found here: https://docs.microsoft.com/en-us/dax/percentile-exc-function-dax

And a simple example of how to use them can be found here: https://dash-intel.com/powerbi/statistical_functions_percentile.php

Warm regards,

Clayton

[MFelix · Accepted solution]

Hi @ar13

Check the link below with an explanation and demo of the rolling 12 months.

http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-powe...

Regards,
MFelix