Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Anonymous
Not applicable

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 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!! 

2 ACCEPTED SOLUTIONS
v-janeyg-msft
Community Support
Community Support

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]
    )

 

vjaneygmsft_0-1643964184157.png

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

 

View solution in original post

Hello@Anonymous 

 

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.

vjaneygmsft_0-1644198743691.png

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

View solution in original post

10 REPLIES 10
ValtteriN
Super User
Super User

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:
ValtteriN_0-1644049694509.png

 

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/ 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




v-janeyg-msft
Community Support
Community Support

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]
    )

 

vjaneygmsft_0-1643964184157.png

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

 

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!

SJR147
Frequent Visitor

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.

Anonymous
Not applicable

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

Anonymous
Not applicable

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. 

@Anonymous  Any updates?

Hello@Anonymous 

 

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.

vjaneygmsft_0-1644198743691.png

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
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.