Forum Discussion

learner03's avatar
learner03
Post Partisan
4 years ago
Solved

Time difference between Today and first timestamp

I have 2 Table-

Receipt Jobs
 
Receipt ID
123
345
567
789

and

Receipt_lines  
   
Recepit IDItem codetimestamp
123abc7/02/2022 6:59
345def7/02/2022 6:56
123def3/02/2022 11:47
567abc3/02/2022 13:34
345xyz4/02/2022 6:20
789abc2/02/2022 14:20
123abc3/02/2022 9:40
567xyz2/02/2022 14:22

 

I need below Output- The time difference in number of days based on when the receipting started in Receipt_lines and today. Also, subtracting weekend and holidays. I have a columnm in Calender table where it says 1 for weekday and 0 for weekend and holiday.

Output 
  
Receipt IDTime difference between today and FIRST timestamp from Receipt_lines table
123 
345 
567 
789 
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi learner03 ,

     

    Please try:

    Count = 
var _first=CALCULATE(MIN('Receipt_lines'[timestamp]),ALLEXCEPT(Receipt_lines,Receipt_lines[Recepit ID]))
return CALCULATE(COUNTROWS('Calendar'),FILTER('Calendar', [Date]>=_first && [Date]<=NOW()&& [Flag]=1 ))
    Measure = CALCULATE([Count], FILTER('Receipt_lines',[Recepit ID]=MAX('Receipt Jobs'[Receipt ID])))

     

    Output:

     

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

4 Replies

    • learner03's avatar
      learner03
      Post Partisan
      4 years ago

      littlemojopuppy 
      Actually , I want to highlight those receipt ID where the job started more than 3 days ago. So, I have reached to the stage where I can highlight those lines that started 3 days ago but I am not able to workout regarding how can I deduct weekends and holidays based on my calender. 
      I have calender table where it shows 1 for weekday and 0 for weekend.
       The column that I have created for more than 3 days is-

      Difference =
      var tstamp=CALCULATE(MIN('Receipt_lines'[Timestamp]),FILTER(ALL('Receipt_lines'),'Receipt ID'[ReceiptID]='Receipt_lines'[ReceiptID]))
      var diff=datediff(tstamp,TODAY(),day)
      return
      if(diff>3,"Red")
      • littlemojopuppy's avatar
        littlemojopuppy
        Community Champion
        4 years ago

        learner03 @I'm curious why you're creating a calculated column for this.  You always want to use measures.  Always.

         

        You have a field for IsWeekday in your date table.  Add another field for IsHoliday.  And if your calendar is the same as an American holiday calendar, it's kind of a pain because certain dates are holidays (Christmas, December 25; Fourth of July, July 4) and others that are relative (Thanksgiving, fourth Thursday in November).


        You should filter dates for (weekend is false) && (holiday is false) and for within the three day period.  I'm typing this on an iPad so kind of limited but I would think something like TODAY()-3 would work.

         

        With those dates filtered,, you should be able to use a CALCULCATE function for MIN(ReceiptLines) with the filtered dates as a parameter.

         

        Hope this helps!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi learner03 ,

     

    Please try:

    Count = 
var _first=CALCULATE(MIN('Receipt_lines'[timestamp]),ALLEXCEPT(Receipt_lines,Receipt_lines[Recepit ID]))
return CALCULATE(COUNTROWS('Calendar'),FILTER('Calendar', [Date]>=_first && [Date]<=NOW()&& [Flag]=1 ))
    Measure = CALCULATE([Count], FILTER('Receipt_lines',[Recepit ID]=MAX('Receipt Jobs'[Receipt ID])))

     

    Output:

     

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