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 ID Item code timestamp 123 abc 7/02/2022 6:59 345 def ...
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.

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!