Forum Discussion

manideep547's avatar
manideep547
Icon for Helper III rankHelper III
6 years ago
Solved

how to get the date difference

Date difference between the current date(selected values(table  A[date])) and previous date with if condition like 
if(datediff(selectedvalues(tableA[date]),earlier(tableA[date]),month,"S","L") in measure 

 

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    I create a table to test:

    Then create a calculated date table:

    Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

    Create two measures:

    Measure = DATEDIFF(MIN('Table'[Date]),MIN('Date'[Date]),MONTH)
    Measure 2 = DATEDIFF(MAX('Table'[Date]),MAX('Date'[Date]),MONTH)

    And then continue to create a measure to show each ID's status:

    Status = IF([Measure]>0||[Measure 2]>0,SWITCH(true,([Measure]>=0&&[Measure]<6)||([Measure 2]>=0&&[Measure 2]<6),"S",[Measure]>=6||[Measure 2]>=6,"L"),"N")

    The result shows:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

3 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi,

     

    I am confused about how you define the previous date.

    If you want to get the difference between previous one month date and current date(today), please try this measure:

     

    Measure =
IF (
    DATEDIFF (
        SELECTEDVALUE ( 'Table'[Date] ),
        IF (
            MONTH ( TODAY () ) = 1,
            DATE ( YEAR ( TODAY () ) - 1, 12, DAY ( TODAY () ) ),
            DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) )
        ),
        MONTH
    ),
    "S",
    "L"
)

     

    And it shows:

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

    • manideep547's avatar
      manideep547
      Icon for Helper III rankHelper III
      6 years ago

      v-gizhi-msft 
      ID   DATE  (DD/MM/YYYY)
      1     02/08/2017
      3     02/01/2017

      1       02/01/2018
      2       08/12/2018
      3      09/12/2018


       
      If the customer not having any transaction in the past considered as an N
      if the customer having a transaction in the selected period in the slicer  and also having a transaction in past within the 6 months period then considers as S
      if the customer having  a transaction in the selected period in the slicer  and also having a transaction in the past +6months period then considers as L

       

      IN slicer I selected the date from 1/1/2018 to 1/12/2018 then

      Here date different I want is for ID '1 '  consider as an A
      for Id' 2' consider as an N and Id 3 considers as L


      Thanks & regards 
      CH Mani deep

       

      • v-gizhi-msft's avatar
        v-gizhi-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi,

         

        I create a table to test:

        Then create a calculated date table:

        Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

        Create two measures:

        Measure = DATEDIFF(MIN('Table'[Date]),MIN('Date'[Date]),MONTH)
        Measure 2 = DATEDIFF(MAX('Table'[Date]),MAX('Date'[Date]),MONTH)

        And then continue to create a measure to show each ID's status:

        Status = IF([Measure]>0||[Measure 2]>0,SWITCH(true,([Measure]>=0&&[Measure]<6)||([Measure 2]>=0&&[Measure 2]<6),"S",[Measure]>=6||[Measure 2]>=6,"L"),"N")

        The result shows:

        Here is my test pbix file:

        pbix 

        Hope this helps.

         

        Best Regards,

        Giotto Zhi