Forum Discussion

JMHenriques's avatar
JMHenriques
Frequent Visitor
2 years ago
Solved

get working days since datetime until NOW with decimals

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? 
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

6 Replies

  • CKN's avatar
    CKN
    Frequent Visitor
    2 years ago

    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.

  • CKN's avatar
    CKN
    Frequent Visitor
    2 years ago

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

    • JMHenriques's avatar
      JMHenriques
      Frequent Visitor
      2 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      JMHenriques
      Frequent Visitor
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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