aggregate measure based on future week number

[Anonymous]

Hi All,

Column 1 is week number and Column 2  is the total sales per week. 

I am trying to created Column 3 , which is average of future 4 weeks , including the current week value.

I tried the following , but this is not working:

Weekly_Forecast = CALCULATE(SUM('Table1'[Forecast])) * CALCULATE(SUM('Table2'[Weekly%]))

Future_Average_forecast_sales = CALCULATE([Weekly_Forecast], ISONORAFTER('Date'[Week No], 'Date'[Week No]+3 , DESC))

Please see the excel :

Thank you very much

[Anonymous · Accepted solution]

@Fowmy @nandukrishnavs Thank you very much for support and help here. 

Final measure looks like as below and generates the desired result as shared in my original post.

Future_Forecast =

VAR _CURRENTWEEK = SELECTEDVALUE('Date'[Week Num])

VAR _DATE = FILTER(

ALL('Date'[Week Num]),

'Date'[Week Num] >= _CURRENTWEEK && 'Date'[Week Num] <= _CURRENTWEEK + 3

)

RETURN

AVERAGEX(_DATE, [Weekly_Forecast])

[Fowmy]

@Anonymous 

Add this as  a new column:

Future Avg Forecast Sales = 
VAR _CURRENTWEEK = [Week No] 
VAR _DATE =  FILTER(
     'Table 2',
     'Table 2'[Week No]>= _CURRENTWEEK && 'Table 2'[Week No] <= _CURRENTWEEK + 3
    )
RETURN
IF(
    COUNTROWS(_DATE)<4,BLANK(),
        AVERAGEX(
        _DATE,
        'Table 2'[Weekly_Forecast]
    )
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

@Fowmy At column level, it gives me average of all the Product in my table. 
How can I filter based on the Product --> Week --> AVERAGEX

Is it also dependednt on the Data Model ? 

I tried the following, using ALLSELECTED ? 

Future Avg Forecast Sales = 
VAR _CURRENTWEEK = [Week No] 
VAR _DATE =  FILTER(
     ALLSELECTED('Table 2', ProductName),
     'Table 2'[Week No]>= _CURRENTWEEK && 'Table 2'[Week No] <= _CURRENTWEEK + 3
    )
RETURN
IF(
    COUNTROWS(_DATE)<4,BLANK(),
        AVERAGEX(
        _DATE,
        'Table 2'[Weekly_Forecast]
    )
)

[Fowmy]

@Anonymous 

I am not sure about the issue you are facing now. Can you explain further or share the PBIX file with the expected results.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

@Fowmy I don't see any option to attached file here, using WeTransfer, I have shared a file in you Email box.

4 week avg : is the desired result in my file.

You can download from here
https://we.tl/t-8JKJ6wr6Rq

Thanks

[Fowmy]

@Anonymous 


You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

@Fowmy Sure. For this purpose , I have shared using WeTransfer, please downlaod the file using below link.

https://we.tl/t-8JKJ6wr6Rq

Regards

[Anonymous]

@Fowmy  I need a Measure, I tried with modification but it did not work. 

I made the follwing changes :

Future Avg Forecast Sales =

VAR _CURRENTWEEK = SELECTEDVALUE('Date'[Week Num])

VAR _DATE = FILTER(

ALL('Date'[Week Num]),

'Date'[Week Num] >= _CURRENTWEEK && 'Date'[Week Num] <= _CURRENTWEEK + 3

)

RETURN

IF(

COUNTROWS(_DATE) < 4, BLANK(),

AVERAGEX(

_DATE,

[Weekly_Forecast]

)

)

[nandukrishnavs]

@Anonymous 

Use below DAX measure. This should work.

FutureAVGMeasure = 
VAR __currentweek =
    SELECTEDVALUE ( Table1[Week No] )
VAR __result =
    AVERAGEX (
        FILTER (
            ALL ( Table1 ),
            Table1[Week No] < __currentweek + 4 && Table1[Week No]>=__currentweek
        ),
        Table1[Weekly Forecast]
    )
RETURN
    __result

Note: For other's reference, I have corrected my previous reply. 

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Fowmy]

@Anonymous 

Try:

Avg Measure = 

    VAR _CURRENTWEEK = SELECTEDVALUE('DATE'[Week No]) 
    VAR _DATE =  FILTER(
        'DATE',
        'DATE'[Week No]= _CURRENTWEEK && 'DATE'[Week No] <= _CURRENTWEEK + 3
        )
    RETURN
    IF(
        COUNTROWS(_DATE)<4,BLANK(),
            AVERAGEX(
            _DATE,
            SUM('Table 2'[Weekly_Forecast])
        )
    )

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Anonymous · Accepted solution]

@Fowmy @nandukrishnavs Thank you very much for support and help here. 

Final measure looks like as below and generates the desired result as shared in my original post.

Future_Forecast =

VAR _CURRENTWEEK = SELECTEDVALUE('Date'[Week Num])

VAR _DATE = FILTER(

ALL('Date'[Week Num]),

'Date'[Week Num] >= _CURRENTWEEK && 'Date'[Week Num] <= _CURRENTWEEK + 3

)

RETURN

AVERAGEX(_DATE, [Weekly_Forecast])

[Fowmy · Accepted solution]

@Anonymous 

Great!

You can now accept the reply as a solution and click on the Thumbs-Sup icon if you like it.

Thanks

Fowmy

[nandukrishnavs]

@Anonymous 

If you are looking for a calculated column, use below DAX.

FutureAVG = 
AVERAGEX (
    FILTER (
        Table1,
        Table1[Week No]
            < EARLIER ( Table1[Week No] ) + 4
            && Table1[Week No]
                >= EARLIER ( Table1[Week No] )
    ),
    Table1[Weekly Forecast]
)

If you are looking for a measure, use below DAX.

FutureAVGMeasure =
VAR __currentweek =
    SELECTEDVALUE ( Table1[Week No] )
VAR __result =
    AVERAGEX (
        FILTER (
            ALL ( Table1 ),
            Table1[Week No] < __currentweek + 4
                && Table1[Week No] >= __currentweek
        ),
        Table1[Weekly Forecast]
    )
RETURN
    __result

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Anonymous]

@nandukrishnavs : Thanks for the response, but this is not working for now. Can you please suggest as per below

First row output should be = (24+45+23+67) / 4 = 39.75....i.e Average(w1,w2,w3,w4) , Second row Average(w2,w3,w4,w5).....so on

I will prefer measure.. Thanks  

I tired to modify a bit , but not working still : 

FutureAVGMeasure =

VAR __currentweek =

SELECTEDVALUE ('Date'[Week Num])

VAR __result =

AVERAGEX (

FILTER (

'Date' ,

// 'Date'[Week Num] <= __currentweek + 3,

'Date'[Week Num] >= __currentweek && 'Date'[Week Num] <= __currentweek + 3

),

[Weekly_Forecast]

)

RETURN

__result