cancel
Showing results for 
Search instead for 
Did you mean: 
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

21 REPLIES 21
ricjmagalhaes
Advocate I
Advocate I

DATEDIFF(<OriginalDate>, TODAY(), DAY)
allankwan
Frequent Visitor

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.

Mike_Carlo
Super User
Super User

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

@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!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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
Frequent Visitor

@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!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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

Generally you create a column flag on your date as to whether it is a business day or not using WEEKDAY. Then you could use that as a FILTER.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

@Greg_Deckler 

i've sucessfully added a column which is calculating days between Start Date & End Date. My question is, wherever there is a blank cell in End Date, i want this cell to be considered as today() and calculations continued.

 

how can it be done

image.png

@nexami @Greg_Deckler Did you ever figure this out? I need to do the same thing. 

How about another step further and ending with the number of business days between two date, so exlcuding weekends? Thank you in advance!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors