cancel
Showing results 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 name Hour Category Title Avg. Am. Sales February 13 1 A 1 February 19 1 B 2 February 17 1 C 2 February 14 1 A 1 March 13 2 A 1

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+Time Category Title Forecast Am. Sales 10.02.2024 13:30 1 A 1 27.03.2023 13:45 2 B 1 01.02.2023 17:01 1 C 2

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!

2 REPLIES 2
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...

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors