Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Employee
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
Employee
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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.