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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
irnm8dn
Post Prodigy
Post Prodigy

Day Count between two dates - DAX Help

I have seen a number of posts about calculating the day count between two date.  None seem to address my specific use case.

 

I am tring to calculate the day count between a "Creation Date" and "Start Date".  Both are columns clearly identified in my dataset. Hoping someone from the community can help.  Those who have done it before, likely have an easy solution/suggestion.

 

I would appreciate any detailed instruction about it being a Measurment, Column, etc.  

 

Thanks in advance!

1 ACCEPTED SOLUTION

Hi @irnm8dn,

 

If you can't ensure which date column with be the small one, you should add some operation to get the specificity one.

 

As I write above, min and max function can used to auto return the matched date.

 

I build a sample table with random date store in date1 and date2, then I can use above formula to get the diff between these date column.

 

Table formula:

Sample = ADDCOLUMNS(GENERATESERIES(1,100,1),"Date1",RANDBETWEEN(DATE(2015,1,1),TODAY()),"Date2",RANDBETWEEN(DATE(2015,1,1),TODAY()))

 

Calculate column:

Diff = DATEDIFF(MIN([Date1],[Date2]),MAX([Date1],[Date2]),DAY) 

 

 

Reuslt:

9.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

9 REPLIES 9
CanadaMGB
Frequent Visitor

Would converting the dates to age in power query work for you? I've used the instructions from this post successfully

 

https://community.powerbi.com/t5/Desktop/Difference-between-two-dates-DAX/td-p/157320

 

@CanadaMGB

 

Thanks for the feedback, though I am not sure this is a solution.  Based on the instructions it appears the solution you posted is giving me a day count between today and the Start Date.

 

In my use case, I need to simply calc the day count between two specific columns.

Hi @irnm8dn,

 

Based on your description, I think DATEDIFF function will suitable for your requirement.

Sample: calculate column.

 

Day Count = DATEDIFF(MIN([Date 1],[Date 2]),MAX([Date 1],[Date 2]),DAY)

DATEDIFF Function (DAX)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft

 

This seems like it's a usable solution - however I noticed that in some cases I have a start date greater than an end date in some of the rows.  Is there away to accommodate this in the DAX statement so that it will identify these rows and simply "error" but show results where it will work - like in the paste from excel below?

 

 

Capture.PNG 

 

Thanks!

Hi @irnm8dn,


If you can ensure which date column stored the min date value, you can direct use datediff function without other functions.

Calculated column: datediff(mindate, maxdate, unit)

Day Count = DATEDIFF([Min Date Column],[Max Date column],DAY)

 Did you test in excel? If this is a case, you can try to use column index calculate. (for e.g. DATEDIF(A2,B2,"D"))

7.PNG

 

DATEDIF function

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Working . Thanks for sharing.

@v-shex-msft

 

Thanks for the additional input.  As I mentioned in my previous post - the issue seems to be that I have min dates greater than max dates which is throughing an error "In DATEDIFF function, the start date cannot be greater than the end date".  This identifies an issue within the data itself.

 

I see the errors when I tested in Excel, which is the screen capture I included.  Your excel screen capture rightfully has the Start Dates being after the End Dates.

 

I was wondering why Power BI doesn't process the error, and rather halts the DAX command, and more importantly if there is a way around it?

Hi @irnm8dn,

 

If you can't ensure which date column with be the small one, you should add some operation to get the specificity one.

 

As I write above, min and max function can used to auto return the matched date.

 

I build a sample table with random date store in date1 and date2, then I can use above formula to get the diff between these date column.

 

Table formula:

Sample = ADDCOLUMNS(GENERATESERIES(1,100,1),"Date1",RANDBETWEEN(DATE(2015,1,1),TODAY()),"Date2",RANDBETWEEN(DATE(2015,1,1),TODAY()))

 

Calculate column:

Diff = DATEDIFF(MIN([Date1],[Date2]),MAX([Date1],[Date2]),DAY) 

 

 

Reuslt:

9.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

What its doing is exploiting the aging function in a different way, taking the difference between today and both the start and end dates individually, which when subtracted gives you a numerical difference you can work with. See image below, I made a copy of the original start and end date columns and aged them per the instructions. The difference is the days between them.

 

snagit1.jpg 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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