Skip to main content
cancel
Showing results for 
Search instead 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

Reply
domdom
Helper II
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
v-yuezhe-msft
Microsoft Employee
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.

View solution in original post

5 REPLIES 5
v-yuezhe-msft
Microsoft Employee
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.

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
Community Champion
Community Champion

just apply filter on visual level?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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
Community Champion
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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.