Forum Discussion

steveplatz's avatar
steveplatz
Frequent Visitor
9 years ago

Rolling Average Past Current Month

I've used the new Quick Measures feature of Power BI to build a 3 month rolling average calculation and it's working well. The equation is displayed below. However, when I try to use this metric in a...
Need Help
dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
9 years ago

I've had similar issues and have resorted to altering the query that creates the calendar to only have dates up to the current date or month.  In Edit Queries -> Advanced Editor, you will need to put in a line like the following:

 

1    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Start of Month",{{"Date", type date}}),
2    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] <= Date.From(DateTime.LocalNow()))
in
3    #"Filtered Rows"

(remove the line numbers before using the code, they are there for me to explain what is going on)

 

 

Line 2 is the new code. #"Changed Type" refers to the name of line 1.  You will need to change your query to reflect this.

Line 3 in your query will likely have the same name as line 1.  You will need to rename line 3 to reflect line 2's name.

 

However, if you are building your date table in DAX, you can make a dynamic calendar thusly:

 

DateTab = CALENDAR("1/1/2010", TODAY())

Your starting date may vary.

 

 

Hope this helps.

David

steveplatz's avatar
steveplatz
Frequent Visitor
9 years ago

Hi David,

 

I am building my date table via DAX and tried what you suggested, but it resulted in the same chart that I shared earlier. I've also got a filter on the page to only include dates in the current month or 12 months prior, but that doesn't seem to do the trick for the rolling average view.

 

Steve

  • dedelman_clng's avatar
    dedelman_clng
    Icon for Community Champion rankCommunity Champion
    9 years ago

    I have found that some Time Intelligence functions don't play nicely with "incomplete" date tables.  I used the quick measure in an existing report of mine and I am experiencing the same behavior as you.  Since this is a preview feature this may be a bug or an unexpected feature - you should report it using the feedback button. 

     

    I got it to at least calculate 0 for future months by removing 'Date'[Date].[QuarterNo] and 'Date'[Date].[MonthNo] from the SUMMARIZE function, but I can't get June and July to disappear.

     

    Sorry I couldn't be more help.