cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tiago_Varela Regular Visitor
Regular Visitor

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

Accepted Solutions
Microsoft v-danhe-msft
Microsoft

Re: aplly a average of the last 3 years to next year

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

Highlighted
Microsoft v-danhe-msft
Microsoft

Re: aplly a average of the last 3 years to next year

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
Microsoft v-danhe-msft
Microsoft

Re: aplly a average of the last 3 years to next year

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.
Tiago_Varela Regular Visitor
Regular Visitor

Re: aplly a average of the last 3 years to next year

@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

 

Microsoft v-danhe-msft
Microsoft

Re: aplly a average of the last 3 years to next year

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

Highlighted
Microsoft v-danhe-msft
Microsoft

Re: aplly a average of the last 3 years to next year

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

Tiago_Varela Regular Visitor
Regular Visitor

Re: aplly a average of the last 3 years to next year

Thanks

It works fine.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors