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

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