calculate number of days between 2 dates

[Anonymous]

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?

[ryanhoss]

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]

DATEDIFF(<OriginalDate>, TODAY(), DAY)

[Anonymous]

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]

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.

[Mike_Carlo]

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.

[MS_1234]

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

[Seth_C_Bauer · 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.

[EPenchev]

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:

[kjensen]

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.

[kjensen]

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

[vjnvinod]

@Seth_C_Bauer 

any reason for multiplying the date with 1?

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

[Seth_C_Bauer]

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

[Greg_Deckler]

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

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

[FW94]

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?

[sammi1244]

@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.

[FW94]

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 😄 

[ashlibek]

Thank you sir, you made my day 😊😊😊

[kristy_decicco]

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?  

[Greg_Deckler]

How about this:

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

[amolg24]

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