Time difference between Today and first timestamp

[learner03]

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	7/02/2022 6:56
123	def	3/02/2022 11:47
567	abc	3/02/2022 13:34
345	xyz	4/02/2022 6:20
789	abc	2/02/2022 14:20
123	abc	3/02/2022 9:40
567	xyz	2/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 ID	Time difference between today and FIRST timestamp from Receipt_lines table
123
345
567
789

[Anonymous · Accepted solution]

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.

[littlemojopuppy]

@learner03 I want to help.  I do.

What have you tried?  Can you provide some background?

[learner03]

@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]

@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!