Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

calculate number of days between 2 dates

I have a table with customer information, including a target start date of the contract and the actual start date. I would like to add an additional column showing the number of days the contract actually started before (negative number) of after (positive number) the target start date. I can't figure out how to do that. Do I need DAX formula's?

1 ACCEPTED SOLUTION

@Anonymous You can create a calculated column. Right click on the table, select "New Column", in the formula section you can use

Column Name = 1. * (Table[actual start date] - Table[target start date])

 

This should give you the negative number of days between the two dates.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

25 REPLIES 25
ryanhoss
Frequent Visitor

To calculate the difference between the target start date and the actual start date in a new column, you don't necessarily need DAX formulas. You can create a new column using a basic subtraction operation in Power BI.

Follow these steps:

  1. In the Data view of Power BI, select the table with the customer information.

  2. Click on the 'New Column' button in the ribbon.

  3. In the formula bar, enter a column name, such as "Date Difference", followed by an equal sign.

  4. Subtract the target start date column from the actual start date column, like this:

    Date Difference = 'YourTableName'[Actual Start Date] - 'YourTableName'[Target Start Date]

Replace 'YourTableName' with the name of the table containing the columns.

This new column will show the difference in days between the actual start date and the target start date, with positive numbers indicating that the contract started after the target start date and negative numbers indicating that it started before the target start date.

Remember to press Enter after typing the formula to apply the new column.

Power BI user and Mac lover

 

ricjmagalhaes
Advocate I
Advocate I

DATEDIFF(<OriginalDate>, TODAY(), DAY)
Anonymous
Not applicable

How about between the date and the month end?

For example, my start date is 7 June and I like to get the number of days to 30 June?

audstrich
Regular Visitor

I have had a very frustrating problem with this...

 

I have four columns that are involved in the conditional column I wish to make:

 

Insurance/Service Date/Claim Date/Payment Date.

If the Insurance is "Patient," then they probably paid on the date of service, not the claim date, so I don't want a negative number, and I would like the column to give me the days to pay from service date to payment date.

If they haven't paid yet, I want the column to give me the days to code, from service date to claim date.

If they have paid, I want the column to give me the days it took to pay, from claim date to payment date.

 

Days to Code or Pay = IF(ISBLANK('Billing and Collections'[Payment Date],1.*('Billing and Collections'[Claim Date]-'Billing and Collections'[Service Date]),IF('Billing and Collections'[Insurance Group]="Patient",1*('Billing and Collections'[Payment Date]-'Billing and Collections'[Service Date]),1*('Billing and Collections'[Payment Date]-'Billing and Collections'[Claim Date])))

 

This formula didn't give me the correct value for some of the accounts. I've also tried to make columns for each step, use datediff instead, and just do it in excel and import only the values to power bi. It is very strange- in query editor, it seems to give correct values. but when I go to the Desktop, the values are sometimes wrong. The problem seems to be in the days to pay area. Is it because I am referencing the insurance column which has a text value? Or are the formats of my date columns not the same possibly? Or do I just need to download the most recent update? This has been such a headache! Any help would be greatly appreciated.

You can also use the Dax expression DatesBetween Documentation is found here.

 

Days Between = DATESBETWEEN( 'Table'[Dates], 'Table'[Start Date], 'Table[End Date] )  

 

This will return a single number for the number of days between the two dates.

Mike Carlo ( https://powerbi.tips )
mike@PowerBI.Tips

Hi, Can u please explai the argument "'Table'[Dates]," in the formula. Which date this is?

@Anonymous You can create a calculated column. Right click on the table, select "New Column", in the formula section you can use

Column Name = 1. * (Table[actual start date] - Table[target start date])

 

This should give you the negative number of days between the two dates.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

It is also doable even if the data is not in table/tables.
It is neede Today () function and a writen date both multiplied to 1:

EPenchev_0-1666273555705.png

 

This works if you have the dates within the same table, how do you do it with one date in table 1 and the other date in table 2? Power BI does not let me choose the date in table 2.

How to you do this in one table but pull the dates from that table and another table in the same BI?

@Seth_C_Bauer 

 

any reason for multiplying the date with 1?

1. * (Table[actual start date] - Table[target start date])

 

 

@vjnvinod yes, so that you get the numeric value of the serialdatetime between those two dates.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Greg_Deckler
Super User
Super User

Yes, a DAX formula like this will work assuming that both of your columns are actually Date columns:

 

Column = (Dates2[Date1] - Dates2[Date2])*1. 

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler I appreciate this post is super old now but I have followed your above suggestion as so - (#"Psych data"[Date received] - #"Psych data"[Date sent])*1 - however receive the following error on all of my entries - Expression.Error: A cyclic reference was encountered during evaluation. Both columns are date columns, do you have any suggestions as to where I may be going wrong please?

@FW94 I am fairly new, so excuse me if I am missing something. I noted you have double quotes and why did you add a # -- I am unsure what # means??  So, I suggest you try this:

('Psych data'[Date received] - 'Psych data'[Date sent])*1

Hoping it works.

Hi @sammi1244 thanks for pointing that out. Unsure why I would have used # and ", must have been having a funny 5 minutes. I have now resolved this but as I say, appreciate you reaching out! Enjoy the Power BI journey 😄 

Thank you sir, you made my day 😊😊😊

Thank you for this post.  Can you help me take this a step further?  In a table with data related to projects, I'm evaluating 2 date columns - Planned Gate meeting date (my Start Date) and Actual Gate meeting date (my End Date).  I've added a calculated column to my table with your formula (Planned Gate Date - Actual Gate Date)*1.   However, I have projects where the Actual Gate meeting hasn't been held yet so the date field is currently blank, and for these projects the result is like 42,796.  How can I get nothing or a " - " to show in the calculated column for those project rows where there is no Actual Gate Date yet in the table?  

 

 

How about this:

 

Column = IF(ISBLANK([Actual Gate Date]),BLANK(),([Planned Gate Date] - [Actual Gate Date])*1.)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

How about taking it one step further and calculating the number of business days between two dates. Thank you in advance!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.