topic Re: Average of future sales in DAX Commands and Tips

Average of future sales

mrclay82 — Fri, 15 Dec 2023 13:23:41 GMT

Hi,

I have a measure called [future sales] which shows the forecasted sales per week.

I would like to calculate the average of this measure for the next 3 weeks and group the result by week.

So if the table is as below (pretending the week number is currently 30), the outcome should be 17,66 for week 30 (20+15+18)/3:

Week 30, future sales = 10

Week 31, future sales = 20

Week 32, future sales = 15

Week 33, future sales = 18

Week 34, future sales = 12

Figures in red are the desired outcome:

Thanks!

Re: Average of future sales

Fowmy — Sun, 17 Dec 2023 08:04:56 GMT

mrclay82

Average Future Sales Next 3 Weeks = VAR currentweek = SELECTEDVALUE ( yourtable[week] ) RETURN CALCULATE ( AVERAGEX ( VALUES ( 'YourTable'[Week] ), [salesmeasure] ), yourtable[week] > currentweek, yourtable[week] <= currentweek + 3 )

Re: Average of future sales

mrclay82 — Wed, 20 Dec 2023 07:57:59 GMT

Thanks @Fowmy !

It doesn't work all the way, probably due to insufficient info from my part.

What happens is that it is totally overriding my date filters and showing me earlier dates (historical dates) instead of future dates.

I have a page filter that filters the page with a calculated column called 'forecasttable'[thisweek] which I have checked "Y" to only give me the future sales which is imported for this week.

I tried to modify your measure like this:

Average Future Sales Next 3 Weeks =

VAR currentweek =

SELECTEDVALUE ( yourtable[week] )

RETURN

CALCULATE (

AVERAGEX ( VALUES ( 'YourTable'[Week] ), [salesmeasure] ),

yourtable[week] > currentweek,

yourtable[week] <= currentweek + 3,
KEEPFILTERS(‘forecasttable’)

)

This gives me the correct dates (only showing the future, but the measure output is blank.

When modifying the measure like this:

Average Future Sales Next 3 Weeks =

VAR currentweek =

SELECTEDVALUE ( yourtable[week] )

RETURN

CALCULATE (

AVERAGEX ( VALUES ( 'YourTable'[Week] ), [salesmeasure] ),

yourtable[week] > currentweek,

yourtable[week] <= currentweek + 3,
KEEPFILTERS(‘forecasttable’[thisweek])

)

I get the following error: "Cannot convert value 'N' of type Text to type True/False".

Do you know the approach here? Please let me know if you need additional info.

Many thanks!

Re: Average of future sales

Dangar332 — Wed, 20 Dec 2023 09:48:11 GMT

HI, mrclay82

try below

measure = var currentweek = max(tablename[week]) return averagex( filter( all(tablename[week]), tablename[week]> currentweek && tablename[week] <= tablename[week]+3 ) )

Re: Average of future sales

mrclay82 — Wed, 20 Dec 2023 10:42:31 GMT

Hi,

Haven't tried yet but shouldn't my future sales measure be included there?

Re: Average of future sales

Fowmy — Wed, 20 Dec 2023 10:48:06 GMT

mrclay82

Sharing a dummy Power BI file representing your scenario would be beneficial. You can save the Power BI file on Google Drive or any other cloud storage platform and provide the link here. Kindly ensure that permission is granted to open the file.

Re: Average of future sales

Dangar332 — Wed, 20 Dec 2023 10:59:10 GMT

Hi, mrclay82

try updated code i am forgot to write [measure]

measure = var currentweek = max(tablename[week]) return averagex( filter( all(tablename[week]), tablename[week]> currentweek && tablename[week] <= tablename[week]+3 ), [salesmeasure] )

Re: Average of future sales

mrclay82 — Wed, 17 Jan 2024 06:24:35 GMT

Dangar332

This measure overrides the date filters showing me earlier dates (historical dates) instead of future dates. My salesmeasure is a sum of a future sales column, only containing future values. But your measure breaks this showing only historical values with the same output all across.

Re: Average of future sales

mrclay82 — Wed, 17 Jan 2024 06:25:34 GMT

Is there another way? More info from my part? It is a fairy complex data model to replicate.

Re: Average of future sales

mrclay82 — Wed, 17 Jan 2024 07:35:06 GMT

Actually this worked after applying a date filter on the page! Many thanks!

Re: Average of future sales

Fowmy — Wed, 17 Jan 2024 08:56:42 GMT

mrclay82

Can you share some sample data with the desired output to have a clear understanding of your question?
You can either paste your data in the reply box or save it in OneDrive, Google Drive, or any other cloud-sharing platform and share the link here.