Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
How do I solve the attached problem.
I've been given the number of holidays requested by employees and their start request date, and would like to roll the dates forward by individual day, but exclude weekends - or if there is a more flexible way of excluding specific dates, like sat, sun, and mon, or sat, sun, and public holidays that would be great too.
See attached. LHS has report I have, RHS shows the kind of result I would like.
Please help!
https://www.dropbox.com/s/d8dgeuhsqdlslgt/Screenshot%202019-12-09%20at%2001.06.16.png?dl=0
https://www.dropbox.com/s/lb29oqdc6ilyf53/PQ.xlsx?dl=0
Sachin
Solved! Go to Solution.
Hi @sachintandon84
Please see the attached solution containing two extra tables, holiday and dates.
Hi @sachintandon84 ,
Networkdays cannot be used in power bi currently, here I have created a sample by DAX to work around. Please check the following steps as below.
1. Create a date tbale and insert an index column in it.
Date = CALENDARAUTO()
RANKX = RANKX(FILTER(ALL('Date'),WEEKDAY('Date'[Date],2)<=5),'Date'[Date],,ASC,Dense)
2. After that, new a calculated column in our fact table.
enddate =
VAR IND =
CALCULATE (
MAX ( 'Date'[RANKX] ),
FILTER ( 'Date', 'Date'[Date] = 'Table'[Start date] )
)
var days = IF('Table'[Days Requestd]<1,1,'Table'[Days Requestd])
VAR ind2 =
INT( IND + days )
RETURN
CALCULATE ( MAX ( 'Date'[Date] ), FILTER ( 'Date', 'Date'[RANKX] = ind2 ) )
3. In the end, we can get our excepted reuslt by the following formula.
Table 2 =
VAR k =
ADDCOLUMNS (
CROSSJOIN (
SELECTCOLUMNS ( 'Table', "Name", 'Table'[Name], "Days", 'Table'[Days Requestd] ),
FILTER ( CALENDARAUTO (), WEEKDAY ( [Date], 2 ) <= 5 )
),
"nam", [Name]
)
VAR c =
ADDCOLUMNS (
k,
"stad", CALCULATE (
MAX ( 'Table'[Start date] ),
FILTER ( 'Table', 'Table'[Name] = [nam] )
),
"edd", CALCULATE (
MAX ( 'Table'[enddate] ),
FILTER ( 'Table', 'Table'[Name] = [nam] )
)
)
VAR re =
ADDCOLUMNS ( c, "if", IF ( [stad] <= [Date] && [edd] > [Date], 1, BLANK () ) )
RETURN
SELECTCOLUMNS( FILTER ( re, [if] = 1 ),"Na_",[Name],"day",[Days],"Date",[Date])
For more details, please check the pbix as attached.
Thanks Frank!
This is really, really helpful, though I am looking for a PQ solution, as I need to do this in Excel.
Any ideas on how I could do this with M-code?
Unless I use Power Pivot in some way?
Are you able to attach / provide a link to your DAX solution? it might help me think of an M-Code solution for this problem
If you have the solution in Excel, that would also really help!
Sachin
Hi @sachintandon84
Please see the attached solution containing two extra tables, holiday and dates.
Thank you - this was exectly what I was looking for.
Thanks Mariusz,
This is a very clever solution, and will help me a lot!
Thank you again!
Kind regards,
Sachin
Hello @sachintandon84
I assume you need the solution in Excel, right?
Do you have expierience with M-language? If you put a similar function that I created once here as an Idea how it could work, are you able to adapt it by your own?
Bye
Jimmy
Hi Jimmy,
Yes, am looking for an Excel solution for this.
I'm very familiar with M-Code, but couldn't work out a PQ solution for it.
Any ideas on how to do it?
Sachin
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
71 | |
67 | |
25 | |
18 | |
12 |