Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 🙂
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:
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 )
The model can be simplified back to this:
Cheers,
Owen 🙂
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 )
Final output looks similar to yours @ImkeF
Cheers,
Owen
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:
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?
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |