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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
nerra
Helper II
Helper II

Rolling Average showing future dates

HI all,

 

I have a rolling average metric defined on top of a custom date table which should show only rolling 4 weeks (28 days):

 

nerra_1-1626870061465.png

 

The metric is defined as:

1. Average of Open To Close rolling average 2 =

IF(
    ISFILTERED('Date'[Week_Start_Date]),
    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 = LASTDATE('Date'[Week_Start_Date].[Date])
    RETURN
        AVERAGEX(
            DATESBETWEEN(
                'Date'[Week_Start_Date].[Date],
                DATEADD(__LAST_DATE, -28, DAY),
                __LAST_DATE
            ),
            CALCULATE(AVERAGE('ams_auto v_closed_ticket'[Open To Close]))
        )
)
 
The Date table is defined as:
Date =
ADDCOLUMNS (
CALENDAR (DATE(1990,1,1), DATE(2025,12,31)),
"Date_ID", FORMAT ( [Date], "YYYYMMDD" ),
"Week_Start_Date", DATEVALUE([Date] - WEEKDAY([Date],2) +1),
"Year", YEAR ( [Date] )
)
 
So my question is, how can I limit the Rolling Average metric to not show dates in the future?
 
Kind regards,
Nerra
 

 

1 ACCEPTED SOLUTION
nerra
Helper II
Helper II

I've managed to find a partial solution by making the upper filter..

VAR __Calc =
IF(
    ISFILTERED('Date'[Week_Start_Date]),
    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 = LASTDATE('Date'[Week_Start_Date].[Date])
    RETURN
        AVERAGEX(
            DATESBETWEEN(
                'Date'[Week_Start_Date].[Date],
                DATEADD(__LAST_DATE, -28, DAY),
                __LAST_DATE
            ),
            CALCULATE(AVERAGE('ams_auto v_closed_ticket'[Open To Close]))
        )
)
RETURN
IF(MAX('Date'[Week_Start_Date].[Date])>TODAY(),BLANK(),__Calc)
 
Thanks,
N

View solution in original post

1 REPLY 1
nerra
Helper II
Helper II

I've managed to find a partial solution by making the upper filter..

VAR __Calc =
IF(
    ISFILTERED('Date'[Week_Start_Date]),
    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 = LASTDATE('Date'[Week_Start_Date].[Date])
    RETURN
        AVERAGEX(
            DATESBETWEEN(
                'Date'[Week_Start_Date].[Date],
                DATEADD(__LAST_DATE, -28, DAY),
                __LAST_DATE
            ),
            CALCULATE(AVERAGE('ams_auto v_closed_ticket'[Open To Close]))
        )
)
RETURN
IF(MAX('Date'[Week_Start_Date].[Date])>TODAY(),BLANK(),__Calc)
 
Thanks,
N

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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