Do not show future value of AVERAGEX and SUMMARIZE

[Seb_77]

Hello,

I have been removing all future dates from my dashboards with the help of a future value column in the calendar table.

However I am struggling to remove future dates in the below case :

My inventory value stop in April 2020, but the average trailing twelve month continue, I want it to stop in April too.

0_Average_inventory_TTM = 

VAR CALENDAR_YTD = 

CALCULATETABLE(
    'CALENDAR',
    DATESINPERIOD('CALENDAR'[Date],LASTDATE('CALENDAR'[Date]),-1,YEAR),
    'CALENDAR'[FutureDate] = "PAST"
)

VAR CALENDAR_MONTH_YTD =

SUMMARIZE(
    CALENDAR_YTD,
    'CALENDAR'[Year],
    'CALENDAR'[Month],
    "AMOUNT",
    CALCULATE(                              
        [1_AMOUNT_REVERSE_YTD],
        'ACCOUNT FAMILY'[P&L] = "300",
        'CALENDAR'[FutureDate] = "PAST"
    )
)

RETURN

CALCULATE(
    AVERAGEX(CALENDAR_MONTH_YTD,[AMOUNT]),
    'CALENDAR'[FutureDate] = "PAST")

I tried to remove future date with the filter : 

'CALENDAR'[FutureDate] = "PAST", it usually works well but not this time.

Could you help me find a solution ?

Thank you

[danextian · Accepted solution]

 Hi @Seb_77 

You can use relative date filtering. Add a the date field to the visual/page/report filter and filter as in the image below:

[amitchandak · Accepted solution]

Make sure both Inventory Value and Avg TTM is controlled. Or You use relative date calendar or page or visual level relative date filter to have data <= today

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

[danextian · Accepted solution]

 Hi @Seb_77 

You can use relative date filtering. Add a the date field to the visual/page/report filter and filter as in the image below: