Create 4 week rolling flag on previous or next weeks

[jaco1951]

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

[user83]

hello @jaco1951,

how you can create a yearweek column ? 

than you

[v-huizhn-msft]

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]

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

[v-huizhn-msft · Accepted solution]

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