Forum Discussion

jaco1951's avatar
jaco1951
Icon for Helper III rankHelper III
8 years ago
Solved

Create 4 week rolling flag on previous or next weeks

Hi

 

I have table that I want to show forecast values for the next 4 weeks, based on the week selected. Not on todays date.

 

And I alse have a graph where I want to show data for the last 6 weeks based on the week selected. 

 

I think this is pretty forward, but I am still a newbie when it comes to DAX

 

Any help would be much appreciated

Br Espen

Show and Tell
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi jaco1951,

    For what I know, I assume you have a table with forecast value and YearWeek column, you should follow the steps below.

    1. Please create a new table by clicking "New Table" under Modeling on home page, the new table only include [YearWeek] column. Note: there is no relationship between YourTable and new table. Please replace the 'YourTable' as your fact table name.


    YearWeekTable = SELECTCOLUMNS('YourTable',"YearWeek",'YourTable'[YearWeek])


    2. Create two measures to get the selected week in slicer and the Next 4 week value.

     

    SelectedWeek=SELECTEDVALUE('YourTable'[YearWeek])


Next_4_week =[SelectedWeek]+4


    3. Then create another measure to get theforecast values for next weeks.

    Next_4_weekvalues =
CALCULATE (
    MAX ( YourTable[Forecast_value] ),
    FILTER (
        'YourTable',
        AND (
            'YourTable'[YearWeek] >= [SelectedWeek],
            'YourTable'[YearWeek] <= [Next_4_week]
        )
    )
)


    4. Finally, you create a table to display your result. There is a similar thread here for your reference. And please feel free to ask if you have any other question.

    Best Regards,
    Angelia

4 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi jaco1951,

    For what I know, I assume you have a table with forecast value and YearWeek column, you should follow the steps below.

    1. Please create a new table by clicking "New Table" under Modeling on home page, the new table only include [YearWeek] column. Note: there is no relationship between YourTable and new table. Please replace the 'YourTable' as your fact table name.


    YearWeekTable = SELECTCOLUMNS('YourTable',"YearWeek",'YourTable'[YearWeek])


    2. Create two measures to get the selected week in slicer and the Next 4 week value.

     

    SelectedWeek=SELECTEDVALUE('YourTable'[YearWeek])


Next_4_week =[SelectedWeek]+4


    3. Then create another measure to get theforecast values for next weeks.

    Next_4_weekvalues =
CALCULATE (
    MAX ( YourTable[Forecast_value] ),
    FILTER (
        'YourTable',
        AND (
            'YourTable'[YearWeek] >= [SelectedWeek],
            'YourTable'[YearWeek] <= [Next_4_week]
        )
    )
)


    4. Finally, you create a table to display your result. There is a similar thread here for your reference. And please feel free to ask if you have any other question.

    Best Regards,
    Angelia

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi jaco1951,

    Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will benefit from here and we can close the thread.

    Thanks,
    Angelia

    • jaco1951's avatar
      jaco1951
      Icon for Helper III rankHelper III
      8 years ago

      Thank you for the solution, it was helpful with the link to fully understand it.