get working days since datetime until NOW with decimals

[JMHenriques]

Hi all,

I have a Calendar Table with IsWeekend and a table with a "created_at" column in Datetime format.

I need to count the days and hours passed since the "created_at" until the refresh date with NOW(). 

I can do this with the following measure but the problem is that it's giving me whole numbers although I changed it to Decimal.

_measure_Age = 
COUNTROWS (
    FILTER (
        ALL ( CalendarTable ),
        [Date] >= SELECTEDVALUE (Tickets[created_at])
            && [Date] <= TODAY()
            && [IsWeekend] = "No"
    )
)

Is it possible to use decimals in order to get the hours? 

[CKN]

if it's for all weekends (not treating any days such as holidays differently) can you use the figure you already have for hours, divide by 24 for days then by 7 for weeks to get the number of weekends. Round it down and multiply by 48 for hours.

[Anonymous]

Hi @JMHenriques ,

Thanks for @CKN 's reply.

@JMHenriques You'll also need to adjust the number of decimal places if you want the results to be displayed in decimal format.

If you want to calculate the number of hours from the “created_at” of each row to now and the results are displayed as decimals, you can refer to my test.

Measure.

DateDiff = DATEDIFF(MAX('Calendar'[created_at]),UTCNOW(),MINUTE)/60

Best regards,

Mengmeng Li

[JMHenriques]

Thank you @Anonymous, that part worked perfectly for the decimals but I still cannot find a way to make what @CKN suggested of subtracting the weekends... I'am trying to use the COUNTROWS and filter to exclude weekends but no joy for now

[Anonymous · Accepted solution]

Hi @JMHenriques ,

Thanks to @CKN  for the idea.

@JMHenriques Your Tickets table are being filtered because you're using a slicer about “created_at”. When you select a value in the slicer, Tickets table is filtered. If you want to disable this filtering, you can use the Edit Interaction feature.

Best regards,

Mengmeng Li

[CKN]

Can you use a DATEDIFF(calculating hours) then subtract COUTROWS of ISWEEKEND days*24?

[JMHenriques]

Sort of... I get it working with the following code:

_measure_Age_Days Weekends = 
DATEDIFF(MAX(Tickets[created_at]),UTCNOW(),hour)/24 - 
COUNTROWS (
    FILTER (
        ALL ( CalendarTable ),
        [Date] >= SELECTEDVALUE (Tickets[created_at])
            && [Date] <= TODAY()
            && [IsWeekend] = "Yes"
    )
)

but the measure basically destroys the tables I put it into, making data appear that should have been filtered out by the filters directly in the "page filters". And it also takes like 1 minute to process. Probably in count rows is not the way to go.