cancel
Showing results for
Did you mean:
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

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

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

1.I have entered some sample data:

2.Create a calculated column.

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

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])))

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

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.
Highlighted
Microsoft

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

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.
5 REPLIES 5
Microsoft

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

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:

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

AVG = AVERAGEA(Table1[Value])

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:

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

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

@v-danhe-msft

Hi daniel,

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

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

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

1.I have entered some sample data:

2.Create a calculated column.

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

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])))

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

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.
Highlighted
Microsoft

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

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

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

Thanks

It works fine.

Announcements

#### 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?

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

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