Forum Discussion

Herchris7172's avatar
Herchris7172
Regular Visitor
2 years ago
Solved

DAX | Date Diff

Hello Team

Need your help on the below 

I have 2 Tables, one includes Dates, Revised Due Date 

Second is a Calender table Used to define my Selected-Date=SelectedValue(Calender[Date])

 

Need to Calculate the Date Diff. If Revised Due Date is Blank then DateDiff is between Date and Selected Date otherwise it will be Revised Due Date and Selected Date.

 

Am using a Column with sumx but only managing to get the sum of the DateDiff with Revised Due Date 

Column = SUMX(Sheet1,IF(ISBLANK(Sheet1[Revised Due Date]),DATEDIFF(Sheet1[Dates ],Sheet1[Selected-Date],MONTH),DATEDIFF(Sheet1[Dates ],Sheet1[Revised Due Date],MONTH)))
 
Any Advice
 
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  Herchris7172 ,

     

    Here are the steps you can follow：

    1. Create measure.

    Measure =
var _select=SELECTEDVALUE('Calender'[Date])
var _if=
 IF(
    MAX('Table'[Revised Due Date]) =  BLANK(),
    DATEDIFF(
        MAX('Table'[Date]),_select,DAY),
    DATEDIFF(
       MAX('Table'[Revised Due Date]),_select,DAY))
return
DIVIDE(ABS(_if) ,30)

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Read about how to use COALESCE to test for BLANK() and replace it with other values like "TODAY()".

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks for the reply from lbendlin , please allow me to provide another insight: 

    Hi  Herchris7172 ,

     

    Here are the steps you can follow：

    1. Create measure.

    Test =
var _select=SELECTEDVALUE('Calender'[Date])
var _table=
ADDCOLUMNS(
    ALL('Table'),"Datediff",
 IF(
    'Table'[Revised Due Date] =  BLANK(),
    DATEDIFF(
        'Table'[includes Dates],_select,MONTH),
    DATEDIFF(
       'Table'[Revised Due Date],_select,MONTH)))  
return
SUMX(
_table,[Datediff])

    2. Result:

     

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • Herchris7172's avatar
      Herchris7172
      Regular Visitor
      2 years ago

      Thanks for the Reply but am Expecting results like the below. This is in Excel

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  Herchris7172 ,

     

    Here are the steps you can follow：

    1. Create measure.

    Measure =
var _select=SELECTEDVALUE('Calender'[Date])
var _if=
 IF(
    MAX('Table'[Revised Due Date]) =  BLANK(),
    DATEDIFF(
        MAX('Table'[Date]),_select,DAY),
    DATEDIFF(
       MAX('Table'[Revised Due Date]),_select,DAY))
return
DIVIDE(ABS(_if) ,30)

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • Herchris7172's avatar
      Herchris7172
      Regular Visitor
      2 years ago

      Another way I found out is this 

      _months_2 =
      Var Due_Date = SELECTEDVALUE(Sheet1[Dates ])
      Var Revise_Date = SELECTEDVALUE(Sheet1[Revised Due Date])
      Var Results = IF(ISBLANK(Revise_Date), DATEDIFF(Sheet1[Selected-Date],Due_Date,MONTH),DATEDIFF(Sheet1[Selected-Date],Revise_Date,MONTH))
      RETURN Results