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 II

## Rolling 3 month average but not showing average for future dates

Hi there,

I have the following table:

month              value

01/01/2018      40

01/02/2018      30

01/03/2018      50

01/04/2018      45

I'm creating a 3 month rolling average by using a quick measure - selecting "Rolling Average" and selecting

Base Value = Average of value

Date = month

Period = Months

Periods Before = 3

periods after = 0

----

After doing this - I create a visual and put Date on the axis with my new 3 month rolling average quick measure as the value - I get the following table:

01/01/2018      40

01/02/2018      35

01/03/2018      40

01/04/2018      41

01/05/2018      42

01/06/2018      48

01/07/2018      45

However - I don't want to show any dates in this visual past the actual current month (let's assume thats 01/04/2018)

can you let me know if thats possible?

Thanks

1 ACCEPTED SOLUTION
Microsoft Employee

@domdom,

You can create the following column in your table , drag the column to slicer or visual level filter, then set its value to 1.

`checkColumn = IF(OR(YEAR('Table'[Date])<YEAR(NOW()),AND(YEAR('Table'[Date])=YEAR(NOW()),MONTH('Table'[Date])<=MONTH(NOW()))),1,0)`

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Microsoft Employee

@domdom,

You can create the following column in your table , drag the column to slicer or visual level filter, then set its value to 1.

`checkColumn = IF(OR(YEAR('Table'[Date])<YEAR(NOW()),AND(YEAR('Table'[Date])=YEAR(NOW()),MONTH('Table'[Date])<=MONTH(NOW()))),1,0)`

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

This method doesn't work for me, and my Calendar table just have all dates from 2009-1-1 to today(). It doesn't have any date beyond today's date. However my axis of my rolling average still shows all other 2019 months beyond current month (2019 Jan).

Community Champion

just apply filter on visual level?

Thank you for the kudos 🙂

Helper II

What filter can I apply?  It doesnt let me just apply a date filter as it produces an error message on the visual that "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."

but i'm probably doing something wrong?

Community Champion

I haven't used quick measures myself, but my guess is that you need to create date table, and then filter based on that
https://docs.microsoft.com/en-us/power-bi/desktop-date-tables

Thank you for the kudos 🙂