Calculating MAPE but only up to the last Date of Actuals

[chillpill]

I have the below formula which works great for averaging the MAPE of multiple categories, however it messes up the result if I have a future date selected that does not include Actuals. How do I restrict the calculation to only the MAX date of Actuals/Forecast - in this case 4/1/2026 being only what I want to it calculate up to?

AvgMAPE = AVERAGEX(
    VALUES('Table'[Category]),
    CALCULATE(
        1 - ( ABS(SUM(Actuals) - SUM(Forecast)) / SUM(Actuals) )
    )
)

Date |	Category |	Actuals |	Forecast |
01/01/2026 |	A1 |	100 |	400
01/01/2026 |	A2 |	200 |	200
02/01/2026 |	A1 |	300 |	400
02/01/2026 |	A2 |	400 |	500
04/01/2026 |	A1 |	500 |	100
04/01/2026 |	A2 |	600 |	300
05/01/2026 |	A1 |		400
05/01/2026 |	A2 |		500
06/01/2026 |	A1 |		100
06/01/2026 |	A2 |		200

[v-echaithra]

Hi @chillpill ,

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Thank you.

[v-echaithra]

Hi @chillpill ,

Thank you for reaching out to Microsoft Community.
Please check the below PBIX file.

Hope this helps.

[AmiraBedh · Accepted solution]

Hello !

Thank you for posting on MS Fabric community.

Try to use a cutoff date inside the measure then force the calculation to only evaluate rows up to that date.

If your future rows have blank actuals.

With your sample, it should stop at 04/01/2026 and ignore 05/01/2026 and 06/01/2026.

AvgMAPE =
VAR LastActualDate =
CALCULATE(
MAX('Table'[Date]),
FILTER(
ALLSELECTED('Table'),
NOT ISBLANK('Table'[Actuals])
)
)
VAR CurrentRowDate =
MAX('Table'[Date])
RETURN
IF(
CurrentRowDate > LastActualDate,
BLANK(),
AVERAGEX(
VALUES('Table'[Category]),
CALCULATE(
1 - DIVIDE(
ABS(SUM('Table'[Actuals]) - SUM('Table'[Forecast])),
SUM('Table'[Actuals])
),
FILTER(
ALLSELECTED('Table'[Date]),
'Table'[Date] <= LastActualDate
)
)
)
)

[chillpill]

This is a great solution and I follow what's happening, and it works if I only select dates where Actuals exist. If I select all dates available it doesn't provide a value, whereas I am hoping despite what month I choose it will calculate a value. For example:

1. If I select 1/1, 2/1, 3/1, and 4/1 from the date filter I want it to result in the avg MAPE for dates 1/2 - 3/1. 

2. If I select 1/2 - 2/1 it provides the avg MAPE for those two dates

[Ashish_Mathur]

Hi,

Your question is not clear.  If you select jan-Apr, why should data for April be excluded.  There is Actual data for April.

[chillpill]

Apologies - you are right. I meant to say, to be clear, that if I choose a series of dates and that series includes a date that does not have Actuals then it should still calculate the avg mape for all dates in the series that do have actuals. 

Example: I select dates 1/1 - 6/1, but only 1/1 - 4/1 have actuals. I would need it to still calculate/return the avg MAPE for 1/1 - 4/1

[Ashish_Mathur]

Does this measure work?

AvgMAPE = AVERAGEX(FILTER(VALUES('Table'[Category]),SUM(Actuals)>0),CALCULATE(1 - ( ABS(SUM(Actuals) - SUM(Forecast)) / SUM(Actuals) )))

[chillpill]

Thank you! This works when I select all months with Actuals, ie 1/1 - 4/1, but as soon as I include a date that does not have actuals yet, ie 5/1, the average starts decreasing. I was hoping it would calculate the avg MAPE for only months that have actuals, regardless if a month without Actuals is selected. 

[Ashish_Mathur]

Hi,

Please share the download link of the PBI file.

[dedelman_clng]

@chillpill there are multiple ways you can handle this, depending on the use case and specifics about the data set

some examples: 

AvgMAPE = IF(ISBLANK(SUM(Actuals)), BLANK(), [remainder of your measure])

AvgMAPE = IF(SUM([Actuals]) = 0, BLANK(), [remainder of your measure])

AvgMAPE = IF(SELECTEDVALUE([Date]) > TODAY(), BLANK(), [remainder of your measure])

Hope this helps

David