Do not filter data from a measure with a slicer

[Anonymous]

Hi,

I'm trying to display over time a graph with both my KPI value and the 6 month rolling average.

I calculated the 6RMA using the following formula :

CALCULATE(

SUM(CompletedKPIView[KPI Value])/6;

DATESINPERIOD((Campaign[MonthDateTime]); MAX(Campaign[MonthDateTime]); -6; MONTH)

The datas are correct when I display over all the years.
However, when I filter the data over a specific year, the data calculated in the 6RMA are wrong for the 5 first months. PowerBI doesn't include data from the previous year in the calculus when a slicer is used.

I have tried using an ALL(Campaign) at the end of my calculus. It does work but when I filter in my graph, all the years are displayed, not only the selected one.

Thanks for your help

[Anonymous]

DATESINPERIOD will work correctly only on DATE TABLES. I presume that Campaign is not a date table. Please create a proper date table in your model, mark it as such and connect to your fact tables. Then and only then will you get correct answers from time-intel functions.

Best
D

[Anonymous]

This is a Date Tables linked to the fact table.
The datas are correct, but not when a slicer is used. Data calculated between January and May are wrong as the months from the previous year are not all taken into account.

[Anonymous]

@Anonymous, please do yourself a favor and watch this: https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

Many thanks.

Best
D

[Anonymous]

I understood what you meant by proper date table. I managed to solve my problem. Thanks !

[Anonymous]

OK. Good.

Best
D

[Anonymous]

So, you're saying that Campaign is a proper Date table marked as such in the model? If it were, then DATESINPERIOD would overwrite any filters on the table and you'd get a correct result. But this does not happen. What happens is that Power BI makes an intersection of the year selected with the 6M period you're trying to calculate the measure over. Hence I know that you don't have a proper Date table. I've been in this business for much too long not to be able to recognize at first sight that a date table is not in there...

Easy 🙂

Best
D