Forum Discussion

Mahadevaraobc's avatar
Mahadevaraobc
Helper II
6 years ago

Difference between date and run if

Hi i have the following sample table, and i want to achieve the following.Each product can have multiple event nos. 

a) Calculate date difference between 1st and last events for same product

b) Mark all event nos  more than 2 days and less than 30 days for same product as Re-Event, excluding 1st Event.

 

Product Event NoDate
Product 13663328-5017/27/2019
Product 14377204-5027/28/2019
Product 18524894-5017/29/2019
Product 19478521-5017/30/2019
Product 29478521-5026/15/2019
Product 29478521-5032/15/2019
Product 29478521-5078/15/2019
Product 39478521-5086/15/2019
Product 39478521-5095/27/2019
Product 39478521-5107/20/2019
Product 39478521-5117/27/2018
Product 39478521-5123/27/2019
Product 39478521-5143/28/2019
Product 39841725-5012/15/2019
Product 29841725-5026/16/2019
Product 29841725-5034/15/2019
Product 49841725-5053/27/2019
Product 49841725-5062/17/2019
Product 49841725-5073/24/2019
Product 40227975-5013/27/2019
Product 40227975-5026/15/2019
Product 40227975-5043/27/2019
Product 50227975-5067/27/2019
Product 50227975-5071/27/2019
Product 50495169-5017/27/2019
Product 50495169-5047/29/2019
Product 50495169-5057/30/2019
Product 50495169-5067/31/2019
Product 51160976-5016/15/2019
Product 51160976-5022/15/2019
Product 51160976-5032/17/2019
Product 51160976-5047/29/2019
Product 51160976-5057/30/2019
Product 51160976-5063/27/2019
Product 51160976-5073/27/2019
Need Help

6 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi Mahadevaraobc ,

     

    Here is the event span, have to do something else, will be back.

     

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

     

     

    Event Span = DATEDIFF(FIRSTDATE(Event[Date]),LASTDATE(Event[Date]),DAY)

     

     

    • Mahadevaraobc's avatar
      Mahadevaraobc
      Helper II
      6 years ago

      Hi Nathaniel_C,

       

      Thanks for replying, this answers part of my question, could you help me with the other question...

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi Mahadevaraobc ,

    Here you go. Will post code in a minute.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

     

    • Nathaniel_C's avatar
      Nathaniel_C
      Community Champion
      6 years ago

      Mahadevaraobc ,

       

      Interesting issue, hope this helps.

       

      If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
      Nathaniel

       

      Here is my pbix  https://1drv.ms/u/s!AgCd7AyfqZtExjCwBgNzr63TFQaz?e=dg6Nzh

       

      First date = CALCULATE(MIN(Event[Date]),Event[Date],FIRSTDATE(Event[Date]))

Elapsed Time = Var StartDate = CALCULATE(MIN(Event[Date]),Filter(ALLSELECTED(Event),min(Event[Date])))


Return
DATEDIFF(StartDate,Event[First date],DAY)

Is Re-event = IF([Elapsed Time]>3&& [Elapsed Time]<30,"Re-Event","")
Event Span = DATEDIFF(FIRSTDATE(Event[Date]),LASTDATE(Event[Date]),DAY)