11 Week Look Ahead Dynamic Calculation

[emae613]

Hello!

I am stuck and I am really hoping that someone can help me write the DAX for the column to get the right results. Or advise on how to do this. I have a Scheduled Start Date column for when work orders are supposed to begin. For my dashboard/visuals I need to look ahead 11 weeks from Monday of each week and I only want the DAX to return the dates in week 11. I also need it to be dynamic so as the weeks progress it keeps looking ahead at week 11. 

As a side note, I was successful in writing DAX for a Week Start Date and a Week End Date column to group them. Now I am stuck. 

Thank you in advance for you help and advice on how to solve this!! 

[v-janeyg-msft · Accepted solution]

Hi, @emae613 

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

[ValtteriN]

Hi,

Try creating this kind of measure:

11weeks = var _sdate =CALCULATE(MIN('Calendar'[Date]),ALL('Calendar'[Date]),

WEEKDAY('Calendar'[Date],2)=1,

WEEKNUM(TODAY())=WEEKNUM('Calendar'[Date]),

'Calendar'[Date]>TODAY()-7)

var _edate = _sdate + 7*11

var cdate = MAX('Calendar'[Date])

return

IF(cdate>=_sdate&& cdate<=_edate,1,0)

Then place it here:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/ 

[v-janeyg-msft · Accepted solution]

Hi, @emae613 

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

[emae613]

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. 

[v-janeyg-msft]

@emae613 

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

[emae613]

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. 

[v-janeyg-msft]

@emae613  Any updates?

[v-janeyg-msft · Accepted solution]

Hello@emae613 

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

[amitchandak]

@emae613 ,

Week =
var _max1 =  today()  // or use maxx(allselected('Date'), 'Date'[Date]) //
var _stweek = _max1 +-1*WEEKDAY(_max1,2)+1 //add plus 7 for next week start date
var _edweek= _max1+ 7-1*WEEKDAY(_max1,2) +(7*11)
return
calculate(sum(Table[Value]), filter(date, Date[Date] >=_stweek && Date[Date] <=_edweek))
// or use all date
//calculate(sum(Table[Value]), filter(all(date), Date[Date] >=_stweek && Date[Date] <=_edweek))

You might have use independent table in case you select a date

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Also refer

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8