Forum Discussion

Fab117's avatar
Fab117
Helper IV
3 years ago
Solved

Calculate date difference vs today date

Hello,   I'm newbie with Power BI. I'm quite familiar with VBA, but not at all with Dax => PowerQuery requests.   I started building my first Power BI report based on several data base fields, b...
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi Fab117 ,

     

    In Power Query:

    New custom columns:

     

    Variance =
Number.From(Date.From([TargetedDueDate])) - Number.From(Date.From(DateTime.LocalNow()))

// and

ProgressStatus =
if [Progress] = 100 then "Completed"
else if [Variance] > 20 then "On time"
else if [Variance] >= 0 and [Variance] <= 20 then "Due soon"
else if [Variance] < 0 then "Overdue"
else // your escape value e.g. null, "Unknown" etc.

     

     

    Pete

Fab117's avatar
Fab117
Helper IV
3 years ago

Big thanks to both of you for taking time looking at my challenge.

I tried to apply both your porposals, but still not there yet.

 

Here is how I proceed:

1. Click on "Transform data" icon to switch in Power Query mode
2. In "Tasks" data base, I click on "Add Column" tab and "Custom Column" icon
3. I add the formulae's you shared

Pete formulae:

 

Nate formulae:

 

In both cases, I've an error message

Error with Pete formulae:

 

Error with Nate formulae:

 

Would you have still patient to explain me what I do wrong?

 

Tks

 

Fab

 

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi Fab117 ,

     

    My version isn't working for you as you've got a double set of square brackets around your field name:

     

    Remove one set of these brackets and it will work fine.

     

    For the record, Nate's isn't working as he didn't include the first argument in the Table.AddColumns function that declares the table to apply it to. You also have a double set of square brackets around your field name in that implementation too.

     

    Pete

    • Fab117's avatar
      Fab117
      Helper IV
      3 years ago

      Hi Pete,

      This is now working perfectly well.

       

      No issue with the 2nd column "ProgressStatus".

       

      Thank you very much for your help

       

      Fab