Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

11 Week Look Ahead Dynamic Calculation

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...
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    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

     

  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    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 _ Janey

amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous ,

 

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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8