Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to create calculated column for due dates due within next 7 days

Good Morning Power Bi Users! 

 

I would like to create a visual for my metric showing all items that are due within next 7 days.

 

As per below it looks like you cannot add an expression into the value.

Any ideas?

 

Thanks,

 

Need Help
Tips and Tricks
  • BA_Pete's avatar
    BA_Pete
    2 years ago

    Hi Anonymous ,

     

    The first issue is that TODAY() is a DAX function but Power Query uses the M language.

    In Power Query, go to the Add Column tab > Custom Column and paste in this code:

     

    let __todayDate = Date.From(DateTime.LocalNow()) in
if [Due Date] >= __todayDate and [Due Date] <= Date.AddDays(__todayDate, 7) then "Due within 7 days"
else "Not due within 7 days"

     

     

    Pete

4 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    2 years ago

    Hi Anonymous ,

     

    The first issue is that TODAY() is a DAX function but Power Query uses the M language.

    In Power Query, go to the Add Column tab > Custom Column and paste in this code:

     

    let __todayDate = Date.From(DateTime.LocalNow()) in
if [Due Date] >= __todayDate and [Due Date] <= Date.AddDays(__todayDate, 7) then "Due within 7 days"
else "Not due within 7 days"

     

     

    Pete

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Pete, 

      As suggested copied and pasted the formula and now getting the below error:

      Thanks for the help.

       

      • BA_Pete's avatar
        BA_Pete
        Super User
        2 years ago

         

        I missed a space in the second [Due Date].

        I've updated my original answer to correct. Please try now.

         

        Pete