Forum Discussion

giorgiokatr's avatar
giorgiokatr
Icon for Helper V rankHelper V
9 years ago
Solved

Network days between two selected days in slicer

Hello 

i have end of month dates in my dataset

 

Date

29/01/2016

28/02/2016 etc

 

i have this field (Date) in a slicer with between enabled

How can i calculate the network days between the first day of the start month and the last day of the end month

ex i a user selects 29/01/2016-28/02/2016 i want to have 46 working days.

if a user selects 29/01/2016-20/02/2016 (slicer allows that even though there is no 20/02/2016) i want to have 46 working days again

  • OwenAuger's avatar
    OwenAuger
    9 years ago

    giorgiokatr - no problem.

    I would strongly recommend having a date table in order to do a NetWorkDays calculation, otherwise would you have to construct the date table within the DAX measure.

    Something similar to one of the above measures should meet your needs :)

13 Replies

    • giorgiokatr's avatar
      giorgiokatr
      Icon for Helper V rankHelper V
      9 years ago

      thanks! and how i calculate the network days between the two days?

      • ImkeF's avatar
        ImkeF
        Icon for Community Champion rankCommunity Champion
        9 years ago

        Hm, this might be a bit over my head. The only solution I found was using a calculated table, which results in this model:

         

         

        This is the code for the table:

         

        AllDays =
SELECTCOLUMNS (
    GENERATE (
        Sales,
        FILTER (
            ALLNOBLANKROW ( 'Calendar' ),
            'Calendar'[EndOfMonth] = 'Sales'[DateEvent]
        )
    ),
    "Date", 'Calendar'[Date],
    "SalesID", 'Sales'[Event],
    "SalesDate", 'Sales'[DateEvent]
)

         

        This is the measure then:

        NetWorkDays =
CALCULATE (
    COUNTROWS ( AllDays ),
    FILTER (
        ALLEXCEPT ( 'Calendar', 'Calendar'[Month], 'Calendar'[Year] ),
        'Calendar'[YearMonth] >= MIN ( 'Calendar'[YearMonth] )
            && 'Calendar'[YearMonth] <= MAX ( 'Calendar'[YearMonth] )
            && 'Calendar'[DayOfWeek] >= 1
            && 'Calendar'[DayOfWeek] <= 5
    )
)

         

         

        Please find the file here: https://www.dropbox.com/s/9a8jijbc29asx59/PBI_DatesFromWholeMonths2.pbix?dl=0

         

        The challenge was to include the months in those cases where the date filter excluded the date from the sales/event table. Unfortunately I don't know how to do this on the fly. Maybe one of our DAX-experts ( OwenAuger, MattAllington ) can help us out here? Thank you guys!