Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Creating a measure that takes the average for future events

Hey guys,


I have no clue what to do here:


I have a table which calculates the average amount of sales by month, hour, category and title like this:

Month nameHourCategoryTitleAvg. Am. Sales


The measure "average amount of sales" refers to a column in a table and is defined as:

Average amount of sales =CALCULATE(AVERAGE(SALES[SALES]),ALL(Calendar[Date]))


 So far so good. But now I want to use these averages as a forecast for future dates (incl. time). I created a table like this:

Date+TimeCategoryTitleForecast Am. Sales
10.02.2024 13:301A1
27.03.2023 13:452B1
01.02.2023 17:011C2


So, when I put in the measure, it deletes all future entries and does only show sales of the past.


For sure, this is exactly what is meant to happen, but I have absolutely no idea how to create a new measure that calculates an average for past amount sales and uses it as a forecast for a future date and time.


Could you help me out here? Thanks a lot! 

Super User
Super User

But now I want to use these averages as a forecast for future dates (incl. time)

Which forecast methodology are you planning to apply?  Do you care about seasonality? Do you care about weekdays/workdays/holidays? DST?

Frequent Visitor

Thanks for your reply. I think that sounds really interesting and I would try if this makes sense for my model. But as I even fail with just averages for future dates I thought that would be the first step of understanding.

In the meantime I tried a few more measures and when I refer to columns like Date+Time (like ALL(SALES[Date+Time])) it creates Dates in the future that weren't in the original table... I think this ALL-function doesn't work for me here...

Helpful resources

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors