Forum Discussion

Oros's avatar
Oros
Post Prodigy
2 years ago
Solved

Count working days

Hello,
 
I am looking for a measure to count the number of days based on the selected date range in the date slicer.  Thanks.
 
I am definitely missing a filter in this measure because the result is giving me the total posted invoices instead of the total days.
 
COUNT DAYS = COUNT('POSTED INVOICE'[Posting_Date])
 

The selected date range above should result in 2 (days).  But is giving me a result of 5 based on this table below.

 

Need Help
  • Tahreem24's avatar
    Tahreem24
    2 years ago

    Oros  Try this measure:

    Date Try =
     var _max = maxx(ALLSELECTED('financials'),'financials'[Date])
    var _min = minx(ALLSELECTED('financials'),'financials'[Date])
    return
    datediff(_min,_max,day)+1
     

     

5 Replies

  • Tahreem24's avatar
    Tahreem24
    Super User
    2 years ago

    Oros  Try this measure:

    Date Try =
     var _max = maxx(ALLSELECTED('financials'),'financials'[Date])
    var _min = minx(ALLSELECTED('financials'),'financials'[Date])
    return
    datediff(_min,_max,day)+1
     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Ideally, you should create a Calendar Table.  Create a relationship (Many to One and Single) from the Posting Date column of the Invoice Table to the Date column of the Calendar Table.  Build the slicer from the Date column of the Calendar Table.  Write these measures:

    Invoices issued = countrows(Data)

    Number of days on which invoices were issues = countrows(filter(values(Calendar[date]),[Invoices issued]>0)

    Hope this helps.

    • Oros's avatar
      Oros
      Post Prodigy
      2 years ago

      Hi Ashish_Mathur,

       

      Thank you for your reply.  The Invoices issued is correct but it looks like the Number of days is NOT correct.  Maybe I missed something?  Also, is that measure supposed to EXCLUDE the weekends?  Thanks again.

       


       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        You are welcome.  The date slicer should be built from the Calenar Table.