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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MrMarshall
Helper II
Helper II

Quick measure rolling average shows future dates

Today, I have a separate Date table and a Sales table.

This time I was creating a rolling average with a quick measure, using the Sales amount field in the sales table and the primary Date field on the date table. 

 

The problem is when I am trying to filter the year using a slicer, the rolling average graph is showing 12 month forward. This makes sense since it got data from 12 months back. But how do I stop it from showing dates 12m forward?

I can't figure this one out. 

 

Instead of pasting the examples in here, I have shared a simple Power BI file in the link below.

https://thelindata-my.sharepoint.com/:u:/g/personal/andreas_marshall_thelindata_se/EQ05SHWuT2FNlUHK0...

 

The screenshow shows the problem: 

Screenshot_326.png

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@MrMarshall,

 

You may try adding IF.

    RETURN
        IF (
            YEAR ( __LAST_DATE ) IN ALLSELECTED ( 'Calendar'[Year] ),
Community Support Team _ Sam Zha
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

3 REPLIES 3
v-chuncz-msft
Community Support
Community Support

@MrMarshall,

 

You may try adding IF.

    RETURN
        IF (
            YEAR ( __LAST_DATE ) IN ALLSELECTED ( 'Calendar'[Year] ),
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks!

Really saved my day, and week, and year!

Anonymous
Not applicable

How can I do that in my case as shown below?

I don't need the future dates (oct to dec 2020):

 

avg_time.PNG

My DAX for the one measure used in this axis:

 

Count of events 6 MO =
IF(
    ISFILTERED('tb_aog'[INICIO]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    VAR __LAST_DATE = ENDOFMONTH('tb_aog'[INICIO].[Date])
    VAR __DATE_PERIOD =
        DATESBETWEEN(
            'tb_aog'[INICIO].[Date],
            STARTOFMONTH(DATEADD(__LAST_DATE, -5, MONTH)),
            ENDOFMONTH(DATEADD(__LAST_DATE, 0, MONTH))
        )
    RETURN
IF (
YEAR ( __LAST_DATE ) IN ALLSELECTED ( tb_aog[INICIO].[Year] ),
        AVERAGEX(
            CALCULATETABLE(
                SUMMARIZE(
                    VALUES('tb_aog'),
                    'tb_aog'[INICIO].[Year],
                    'tb_aog'[INICIO].[QuarterNo],
                    'tb_aog'[INICIO].[Quarter],
                    'tb_aog'[INICIO].[MonthNo],
                    'tb_aog'[INICIO].[Month]
                ),
                __DATE_PERIOD
            ),
            CALCULATE([Count of events], ALL('tb_aog'[INICIO].[Day]))
        )
))

 

 

 

 

Thanks in advance.

Marcos

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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