Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 "St...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi Anonymous,

     

    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:

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Anonymous,


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"))

 

DATEDIF function

 

Regards,

Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Anonymous

 

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous,

     

    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:

     

    Regards,

    Xiaoxin Sheng