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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

You could also add a column as

 

Variance = Table.AddColumn("Variance", each Duration.Days([TargetedDueDate] - Date.From(DateTime.LocalNow())))

 

to get the integer days difference.

 

--Nate