Average of future sales

[mrclay82]

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!

[Dangar332]

HI, @mrclay82 

try below

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

[mrclay82]

Hi,

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

[Dangar332]

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

[mrclay82]

@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.

[Fowmy · Accepted solution]

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

[mrclay82]

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

[mrclay82]

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!

[Fowmy]

@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.

[mrclay82]

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

[Fowmy]

@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.