Rolling 3 month average but not showing average for future dates

[domdom]

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

[v-yuezhe-msft · Accepted solution]

@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

[fyoung]

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

[Stachu]

just apply filter on visual level?

[domdom]

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?

[Stachu]

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