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

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Anonymous

       

      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?

       

       

       

       

      Thanks!

      • 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

  • CanadaMGB's avatar
    CanadaMGB
    Frequent Visitor
    8 years ago

    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.