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'veA column [TargetedDueDate] with targeted completion date for the task => date fieldA 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

Calculate date difference vs today date | Microsoft Fabric Community

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.

6 Replies

BA_Pete

Super User

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
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
Fab117
Helper IV
3 years ago
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
  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
    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
Fab117
Helper IV
3 years ago
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: