cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## 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, 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

• 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

1 ACCEPTED SOLUTION
Super User

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

6 REPLIES 6
Helper IV

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:

Helper IV

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

Super User

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Helper IV

Hi Pete,

This is now working perfectly well.

No issue with the 2nd column "ProgressStatus".

Thank you very much for your help

Fab

Super User

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

I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Super User

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors