Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
giorgiokatr
Helper V
Helper V

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

@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 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

13 REPLIES 13
ImkeF
Super User
Super User

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

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!

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

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 🙂

 

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 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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 🙂

 

Final output looks similar to yours @ImkeF

Capture.png

 

Cheers,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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 😉

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF Yes I see 🙂

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... 🙂

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

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thank you Owen, you are simply Owesome 😉

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Awesome thanks!!

@OwenAuger i just wanted to calculate the network days and to avoid a new table with dates since my master table has only end of month dates and not every date...

@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 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

thanks @OwenAuger i created a table with dates. Its easier!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.