Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
Solved! Go to 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.
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
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-
@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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
87 | |
79 | |
62 | |
61 | |
60 |
User | Count |
---|---|
166 | |
114 | |
99 | |
73 | |
65 |