Forum Discussion

peteru9067's avatar
peteru9067
Icon for Helper III rankHelper III
5 years ago
Solved

Dax Measures

I am trying to write a dax measures for finding the number of dates between two dates in column A & B....... I do not want to add a calculated column but a dax measures that will perform the differen...
  • Samarth_18's avatar
    Samarth_18
    5 years ago

    You can try below code.

     

    Date_Diff =
    var calculate_diff = DATEDIFF(MAX('Dates_table'[Column A]),MAX('Dates_table'[Column B]),DAY)
    return IF(calculate_diff < 0,calculate_diff * -1,calculate_diff)
     

     

     
  • Samarth_18's avatar
    Samarth_18
    5 years ago

    You can create measure with below code to achive this

     

    Date_Differences =
    VAR calculate_diff =
    DATEDIFF ( MAX ( 'Test'[Column A] ), TODAY(), DAY )
    RETURN
    IF (
    MAX ( 'Test'[Column A] ) <= TODAY ()
    && MAX ( 'Test'[Column B] ) = DATE ( 1899, 12, 31 ),
    calculate_diff,
    FORMAT ( MAX ( 'Test'[Column B] ), "MM/DD/YYYY" )
    )
     
    PFA screenshot of output

     

Samarth_18's avatar
Samarth_18
Icon for Community Champion rankCommunity Champion
5 years ago

You can try below code.

 

Date_Diff =
var calculate_diff = DATEDIFF(MAX('Dates_table'[Column A]),MAX('Dates_table'[Column B]),DAY)
return IF(calculate_diff < 0,calculate_diff * -1,calculate_diff)
 

 

 
  • peteru9067's avatar
    peteru9067
    Icon for Helper III rankHelper III
    5 years ago

    Thank you very much........ that worked perfectly well for me.

  • peteru9067's avatar
    peteru9067
    Icon for Helper III rankHelper III
    5 years ago

    An extension to what I am actually trying to accomplish is calculating overdue dates for Compliance Testing. I can use calculated columns to get it done but I know there is probably a better way to use measures.

    So with both columns Column A is Planned Date and Column B is Completed Date...... in my fact table if the test is yet to be performed column B is 12/31/1899. So if Planned date was 07/01/2021 and completed is 12/31/1899 that means the test is "overdue" by 11 days if counting today.

     

    So here is what I think the code should be:

    IF Column A <= today() && Column B = 12/31/1899, **some planned dates are in the future

    Datediff = Column A, Column B

     

    But what will the DAX Measure look like

    • Samarth_18's avatar
      Samarth_18
      Icon for Community Champion rankCommunity Champion
      5 years ago

      You can create measure with below code to achive this

       

      Date_Differences =
      VAR calculate_diff =
      DATEDIFF ( MAX ( 'Test'[Column A] ), TODAY(), DAY )
      RETURN
      IF (
      MAX ( 'Test'[Column A] ) <= TODAY ()
      && MAX ( 'Test'[Column B] ) = DATE ( 1899, 12, 31 ),
      calculate_diff,
      FORMAT ( MAX ( 'Test'[Column B] ), "MM/DD/YYYY" )
      )
       
      PFA screenshot of output