Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

aggregate measure based on future week number

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

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
    Fowmy
    6 years ago

    Anonymous 

    Great!

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

    Thanks

    Fowmy

13 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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
  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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]
      )
      )
      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        6 years ago

        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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Fowmy
        Icon for Super User rankSuper User
        6 years ago

        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