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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tiago_Varela
Helper I
Helper I

aplly a average of the last 3 years to next year

Hi all,

I'm looking for a DAX  formula that give me the following:

 

I have a table with nº of sales per day from 2014 to 2017

I need to have a average per day of those sales.

Next, I want to forecast to next year that average, so i know that on the 1st of May i'm probabling going to sale 110 biscoits.

 

Can someone help me!

Thanks

 

 

 

2 ACCEPTED SOLUTIONS

Hi @Tiago_Varela,

Based on my test, you can refer to below steps:

1.I have entered some sample data:

1.PNG

2.Create a calculated column.

Month-Day = FORMAT('Table1'[Date],"MMM")&"-"&FORMAT('Table1'[Date],"D")

2.PNG

3.Create a measure, a Card visual and a slicer. Now you could get the average result.

Measure = CALCULATE(AVERAGE(Table1[Value]),FILTER('Table1','Table1'[Month-Day]=MAX('Table1'[Month-Day])))

3.PNG

If you want to forecast the 2019 sales, you just need to use the measure to multiple 10%.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/dtajk8t9z09d0xk/aplly%20a%20average%20of%20the%20last%203%20years%20to%20n...

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @Tiago_Varela,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-danhe-msft
Employee
Employee

Hi @Tiago_Varela,

From your description, I could not figure out which average value you want to get. If you want to get the average value a precise day of a year you could refer to below steps:

Sample data:

1.PNG

Create a measure and a Table visual and you could see the result:

AVG = AVERAGEA(Table1[Value])

2.PNG

If you just want to calculate the average value based on the Month-Day(for example 1st of May), you could refer to this measure:

Measure = CALCULATE('Table1'[AVG],FILTER('Table1',MONTH('Table1'[Date])=MONTH('Table1'[Date])&&DAY('Table1'[Date])=DAY('Table1'[Date])))

Result:

3.PNG

If I misunderstood you, could you please offer me more information about your data structure or share your pbix file if possible?

 

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/wotsqgqeio83968/aplly%20a%20average%20of%20the%20last%203%20years%20to%20n...

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-danhe-msft

 

Hi daniel,

Thanks a lot for your help and answer.

 

What i have is a table with rooms sold from 2014 to 2018.

I need an average per day from those 5 years.

 

Per exemple:

01/01/2015 - 54

02/01/2015 - 60

03/01/2015 - 50

...

28/12/2018 - 30

29/12/2018 - 20

30/12/2018 - 60

31/12/2018 - 40

 

and i need per day the average of those days:

01/01 - ((01/01/15 + 01/01/16 + 01/01/17 + 01/01/18)/4))

 

Because then i need to say that:

That average is the same at 2019 plus or minus a certain percentage

 

01/01/2019 = (average of ((01/01/15 + 01/01/16 + 01/01/17 + 01/01/18)/4))) * 10%

 

Is a way of forecasting the sales for the future.

 

But i cannot do a Dax measure that can help me with this.

Also cannot figure it out how i can show you my pbx file or do a print screen and add it here! Sorry

 

Thanks

 

Hi @Tiago_Varela,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks

It works fine.

Hi @Tiago_Varela,

Based on my test, you can refer to below steps:

1.I have entered some sample data:

1.PNG

2.Create a calculated column.

Month-Day = FORMAT('Table1'[Date],"MMM")&"-"&FORMAT('Table1'[Date],"D")

2.PNG

3.Create a measure, a Card visual and a slicer. Now you could get the average result.

Measure = CALCULATE(AVERAGE(Table1[Value]),FILTER('Table1','Table1'[Month-Day]=MAX('Table1'[Month-Day])))

3.PNG

If you want to forecast the 2019 sales, you just need to use the measure to multiple 10%.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/dtajk8t9z09d0xk/aplly%20a%20average%20of%20the%20last%203%20years%20to%20n...

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.