Forum Discussion

Reimer's avatar
Reimer
Helper II
5 years ago
Solved

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...
Need Help
  • Reimer's avatar
    Reimer
    5 years ago

    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
PC2790's avatar
PC2790
Community Champion
5 years ago

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)
Reimer's avatar
Reimer
Helper II
5 years ago

Well this is more then I can handle 🙂

 

Do I just copy this in my Power BI and change

"DatesTable for my column? 

  • PC2790's avatar
    PC2790
    Community Champion
    5 years ago

    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

    • Reimer's avatar
      Reimer
      Helper II
      5 years ago

       

      Something is still wrong? 

  • PC2790's avatar
    PC2790
    Community Champion
    5 years ago

    Is Invoerdatum your date column?

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

    • Reimer's avatar
      Reimer
      Helper II
      5 years ago

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

       

      I am now using this formula:

       

       

      But this is the output:

       

       

      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?