Calculate date difference (vs "Today()")

[Fab117]

Hello,

I'm newbie with Power BI.

I'm quite familiar with VBA, but not at all with Dax.

I started building my first Power BI report based on several data base fields, but would need to do some calculation with Dax.

I spent time with Microsoft Dax functions definitions (Data Analysis Expressions (DAX) Reference - DAX), but didn't find my solution.

Challenge n°2: Calculate date difference (vs "Today()")

In database "Tasks", I've

• A column [TargetedDueDate] with targeted completion date => date field
• A column [Progress] with a range [0-100]. "100" meaning that task is fully completed

I'd like to create 2 new columns:

• "Variance" where there is calculation: [TargetedDueDate] content – Today () (=> will be positive if still on time and negative if overdue)
• "ProgressStatus" with 3 possible contents:

"On time": If Variance is ">20"​

"Due soon": If Variance is ">=0" and "<=20"​

"Overdue": If Variance is "<0"​

Do someone could help me with the formulae's in both "Variance" and "ProgressStatus" columns?


Thanks a lot


Fab

 

[Fab117]

In case someone else would face same challenge:

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.

Thanks to v-stephen-msft