cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
giorgiokatr Member
Member

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Network days between two selected days in slicer

@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 Smiley Happy



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




13 REPLIES 13
Super User
Super User

Re: Network days between two selected days in slicer

You can wrap your dates into the DAX-functions STARTOFMONTH: https://msdn.microsoft.com/en-us/library/ee634954.aspx and ENDOFMONTH respectively.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




giorgiokatr Member
Member

Re: Network days between two selected days in slicer

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

Super User
Super User

Re: Network days between two selected days in slicer

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

 

PBI_DatesFromWholeMonths1.png

 

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!

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




giorgiokatr Member
Member

Re: Network days between two selected days in slicer

Thanks..i did the same as it seems there is no networkdays function! thanks so much!

Super User
Super User

Re: Network days between two selected days in slicer

Hi @giorgiokatr@ImkeF@MattAllington

 

First of all, you definitely need a Calendar table as per Imke's example.

 

It sounds like the measure you're wanting to create (which I have called NetWorkDays Complete Months) depends on the Calendar table only - regardless of anything in the fact table - if I understand it correctly Smiley Happy

 

I would suggest a measure like this:

  • NetWorkDays Complete Months = 
    CALCULATE (
        COUNTROWS ( 'Calendar' ),
        PARALLELPERIOD ( 'Calendar'[Date], 0, MONTH ),
        'Calendar'[DayOfWeek] <= 4
    )
  • PARALLELPERIOD 'rounds' the date selection at both ends so that it covers complete months (translated by zero months in this case).
  • 'Calendar'[DayOfWeek] <= 4 gives you weekdays (since 5/6 are Sat/Sun).
  • The resulting context will be the intersection of these.

The model can be simplified back to this:

Capture.PNG

 

Sample model here

 

Cheers,

Owen Smiley Happy



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Network days between two selected days in slicer

Thanks Owen, very cool.

Now I vaguely remember an article I read ages ago with warning about this function ... which we now take to our advantage :-)

 

But was I was biting my nails was to be able to incorporate the "Event"-field from the Sales-table into the reports as well. But maybe this was a misinterpretation of the request. (But just in case you're up for it: I would be interested how to calculate that on the fly as well :-) )

 

Cheers, Imke

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: Network days between two selected days in slicer

Oh gotcha - I missed that detail.

 

This measure shows the NetWorkDays in the same way as before but filtered by the dates relating to the selection on the 'Sales' table (same as original measure, just wrapped in an additional CALCULATE with Sales as a filter argument):

 

NetWorkDays Complete Months (Filtered by Sales) = 
CALCULATE (
    CALCULATE (
        COUNTROWS ( 'Calendar' ),
        PARALLELPERIOD ( 'Calendar'[Date], 0, MONTH ),
        'Calendar'[DayOfWeek] <= 4
    ),
    Sales
)

Same link as before Smiley Happy

 

Final output looks similar to yours @ImkeF

Capture.png

 

Cheers,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Network days between two selected days in slicer

Thanks @OwenAuger,

that's true, as long as you don't slice. If you slice until x-mas, we get different results: Mine still has the December in it & yours not:

 

PBI_NetworkdaysBetweenSelectedSlicers.png

 

Cheers, Imke ;-)

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: Network days between two selected days in slicer

@ImkeF Yes I see Smiley Happy

My formula was only including months where there was a row present in the Sales table for the date context from the slicer.

 

My formula could be changed to this. However, Events from the Sales table would also be visible as long as at least one day of the relevant month is selected.

 

NetWorkDays Complete Months (Filtered by Sales) = 
CALCULATE (
    CALCULATE (
        COUNTROWS ( 'Calendar' ),
        PARALLELPERIOD ( 'Calendar'[Date], 0, MONTH ),
        'Calendar'[DayOfWeek] <= 4
    ),
    CALCULATETABLE (
        SUMMARIZE ( Sales, 'Calendar'[Date] ),
        PARALLELPERIOD ( 'Calendar'[Date], 0, MONTH )
    )

This may or may not be useful for the intended report outputs... Smiley Happy

@giorgiokatr - what sort of report output do you want? Do you want items from your fact table appearing alongside the NetWorkDays measure?

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!