cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Reimer
Helper I
Helper I

Next monday

I would like a new calculated column date as follows: if today is monday, keep it. If not return the next monday. 

 

Ive tried this:

NextMonday = DATEADD(Dates[Date],MOD(7-WEEKDAY(Dates[Date],3),7),DAY) 

Power BI give no error, but the new column remains empty. What am I missing? 

1 ACCEPTED SOLUTION
Reimer
Helper I
Helper I

Ive tried this as well. But the formula doesnt keep the monday:

 

Maandag publicatie = 'Procedures - Power BI'[Invoerdatum].[Date] - WEEKDAY('Procedures - Power BI'[Invoerdatum],2)+8

View solution in original post

11 REPLIES 11
PC2790
Solution Sage
Solution Sage

Doesit work for you:

 

MondayColumn =
var NextMonday= DatesTable[Date].[Date]-MOD(DatesTable[Date]-2,7)
return
if(WEEKDAY(DatesTable[Date],2)=1,DatesTable[Date],NextMonday)

Well this is more then I can handle 🙂

 

Do I just copy this in my Power BI and change

"DatesTable for my column? 

Is Invoerdatum your date column?

If yes, then remove the [Date] from everywhere where it is causing the error

Thank you, no more Error. Just two small issues.

 

I am now using this formula:

 

DAX.PNG

 

But this is the output:

 

Dates.PNG

 

So, it return the previous Monday (and should return the next monday), and on Mondays (invoerdatum) it returns a very weird date (and should remain the same)

 

Can you help?

Ahh! my bad. 

yes you need to create a calculated column from ColumnTools --> New Column 

and paste this dax iformula there

And yes DatesTable is Dates[Date] for you

error.PNG

 

Something is still wrong? 

amitchandak
Super User IV
Super User IV

@Reimer ,

This monday = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1

 

Next monday = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+8



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Ive tried this, but is still not quite working. If I use:

 

Next monday = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+8

 

Then all days of the week change in the next monday. But also the mondays change in the next monday. But mondays must remain the same.

 

So:

 

Monday 1 januari --> Monday 1 januari

Thuesday 2 januari --> Monday 8 januari

Wednessday 4 januari --> Monday 8 januari

Etc.

@Reimer , Try this if not resolved yet

Next monday = if(WEEKDAY([Date],2)=1, [Date],'Date'[Date]+ 8-1*WEEKDAY('Date'[Date],2) )


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

WAUW, it is working.

 

Thank you all for your great help and patience! 

Reimer
Helper I
Helper I

Ive tried this as well. But the formula doesnt keep the monday:

 

Maandag publicatie = 'Procedures - Power BI'[Invoerdatum].[Date] - WEEKDAY('Procedures - Power BI'[Invoerdatum],2)+8

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors