Calculate date difference vs today date

[Fab117]

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, but would need to do some calculation with Dax.

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

NB: I also looked at this forum proposal when I types title of this post without finding solution to my challenge 

Challenge n°2: Calculate date difference vs today date

In database "Tasks", I've

• A column [TargetedDueDate] with targeted completion date for the task => 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 – current date (=> will be positive if still on time and negative if overdue)
• "ProgressStatus" with 4 possible contents:

- "On time": If Variance is ">20 days"

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

- "Overdue": If Variance is "<0 days"

- "Completed" if [Progress] field contains "100"

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

Thanks a lot

Fab

[Fab117]

NB: I found the way to issue a Query => for Nate proposal, I also tried:

In Power Query: Tab "Home", Icon "New Source", "More", "Other" and "Blank Query"

But I can't see the new column which should have been created

Same when tried to mention in which data base field [TargetedDueDate] could be found:

[Fab117]

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]

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]

Hi Pete,

This is now working perfectly well.

No issue with the 2nd column "ProgressStatus".

Thank you very much for your help

Fab

[watkinnc]

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

[BA_Pete · Accepted solution]

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