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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
phjz
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
February131A1
February191B2
February171C2
February141A1
March132A1

 

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! 

2 REPLIES 2
lbendlin
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?

phjz
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

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.