Forum Discussion
11 Week Look Ahead Dynamic Calculation
Hi, Anonymous
You can create this summary table, or use it as a table filter in other measures.
Like this:
Two columns first:
weekday = WEEKDAY([Date],2)weeknum = WEEKNUM([Date],1)Table1 = VAR a = MAXX ( FILTER ( ALL ( 'Table' ), [Date] = TODAY () ), [weeknum] ) RETURN SUMMARIZE ( FILTER ( ALL ( 'Table' ), [weeknum] = MAXX ( FILTER ( ALL ( 'Table' ), [weeknum] = a + 11 ), [weeknum] ) ), [Date], [weekday], [weeknum] )Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ JaneyHelloAnonymous
Because I don't know what your requirements are used for, so I just created a table.
You need to create table by new table button.
Reference:
SUMMARIZE function (DAX) - DAX | Microsoft Docs
Can you share your formula and error screenshots in the report? If you can describe in detail how your needs are presented in the report visual, I can help you more specifically.
Best Regards,
Community Support Team _ Janey
Hi, Anonymous
You can create this summary table, or use it as a table filter in other measures.
Like this:
Two columns first:
weekday = WEEKDAY([Date],2)weeknum = WEEKNUM([Date],1)Table1 =
VAR a =
MAXX ( FILTER ( ALL ( 'Table' ), [Date] = TODAY () ), [weeknum] )
RETURN
SUMMARIZE (
FILTER (
ALL ( 'Table' ),
[weeknum]
= MAXX ( FILTER ( ALL ( 'Table' ), [weeknum] = a + 11 ), [weeknum] )
),
[Date],
[weekday],
[weeknum]
)
Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
Thank you! Ok I am very green in Power BI and completely self taught. What is a summary table? I was able to successfully create the first 2 columns as you listed above the Weekday and Weeknum columns. I went to add a new table and when I got to this portion: ( 'Table' ), [Date]
it gave me an error. I was trying to load my Scheduled Start Date table in that spot but it gave me a syntax error. It is loading 'Scheduled Start Date'.Date so I am thinking I am doing something wrong but it is making me select .Date. Hopefully that makes sense.
HelloAnonymous
Because I don't know what your requirements are used for, so I just created a table.
You need to create table by new table button.
Reference:
SUMMARIZE function (DAX) - DAX | Microsoft Docs
Can you share your formula and error screenshots in the report? If you can describe in detail how your needs are presented in the report visual, I can help you more specifically.
Best Regards,
Community Support Team _ JaneyAnonymous Any updates?
Thank you! I was able to create the table. Thank you so much and I apologize for my delayed response to this. So would I just use the scheduled start in the newly created table to filter by the t-week? Also, for some reason I'm not able to copy and paste anything into these text boxes. It gives me an error everytime.
Anonymous
You can have a try. I'm not sure which way you created the table in the end, please explain the problem in detail.
Janey
Can someone please give a little more detailed explanation to the accepted solution? As I read it, the steps are to use Table Tools > New Table, then use New Column to add columns for weekday (using the expression provided) and weeknum (using the expression provided). It appears that a column Date must also be created, but how is the Date column populated - is this meant to be a running list of all dates in a year? Also: in the Table1 expression, where FILTER (ALL ('Table')) is used, does 'Table' indicate the user's separate data table (the name of that table)? Finally, in the Table1 expression, does [Date] refer to the Date column in this summary table or a Date column presumed to exist in the user's separate table. Thanks for any help - I believe this post solves my own need but I'm afraid I don't follow the instructions. Thank you!
I believe I have figured this out: columns weekday and weeknum need to be added to the user's table, using the expressions provided. Date is also a column in the user's table. So in the suggested solution, all entries are pointing to columns in the user's table, except the Return function will create those same column entries in Table1. Hope this helps other new users.
