Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
Any help appreciated.
I need to get the values from the second business Day of the next month for Date1 which looks into Insert_date for the second business date.
Example: Input
Date1 Total INSERT_DATE
11/06/2021 0:00 42 30/06/2021 0:00
11/06/2021 0:00 45 1/07/2021 15:42
11/06/2021 0:00 2 2/07/2021 16:15
11/06/2021 0:00 3 3/07/2021 0:00
14/06/2021 0:00 66 20/06/2021 0:00
14/06/2021 0:00 66 1/07/2021 15:42
14/06/2021 0:00 34 2/07/2021 16:15
14/06/2021 0:00 1 3/07/2021 0:00
14/06/2021 0:00 45 14/07/2021 0:00
14/06/2021 0:00 34 15/07/2021 0:00
17/07/2021 0:00 5 20/07/2021 21:23
17/07/2021 0:00 1 21/07/2021 22:29
17/07/2021 0:00 66 1/08/2021 22:49
17/07/2021 0:00 7 2/08/2021 22:49
17/07/2021 0:00 77 3/08/2021 22:49
17/07/2021 0:00 8 4/08/2021 22:49
Output
Date1 Total Insert_Date
11/06/2021 0:00 2 2/07/2021 16:15
14/06/2021 0:00 34 2/07/2021 16:15
17/07/2021 0:00 77 3/08/2021 22:49
Solved! Go to Solution.
Not sure if you want a measure or a DAX summary table. I recommend you use a measure. Here is one that you can use as a visual-level filter to get your desired result.
Is Second WD =
VAR thisEOM =
EOMONTH ( MIN ( SWD[Date1] ), 0 )
VAR thisInsertDate =
MIN ( SWD[INSERT_DATE] )
VAR vSWD =
MAXX (
TOPN (
2,
CALCULATETABLE (
DISTINCT ( SWD[INSERT_DATE] ),
ALL ( SWD[INSERT_DATE], SWD[Total] ),
SWD[INSERT_DATE] > thisEOM,
WEEKDAY ( SWD[INSERT_DATE] ) IN { 2, 3, 4, 5, 6 }
),
SWD[INSERT_DATE], ASC
),
SWD[INSERT_DATE]
)
VAR result =
IF ( thisInsertDate = vSWD, "Y", "N" )
RETURN
result
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @Anonymous ,
Has your problem been solved? If it is solved, please mark a reply which is helpful to you.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below. Note some of the first steps are to change the datetimes to my locale and clean up the column names to remove extra spaces (from copy/paste).
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZNhCoQgEIWvEv4OnBlHbb1KdLfO0snWbMtNxqxl4cFA6sd7b2gcFaIGpwkIOwgAy9wts+pVmkxpfCsdGTi9UVPf4FiJg9pv99EGpgISL22YnbGKBC9xkoad5ALapp0oI5PMQcq5uA5yTu6HpH5+4GAOliu64Biu+JEKuuBE4V8Kkhcf6fyMUwuGVuD407cymJWDrSv7vCIMZO6gKh1RXhtRoFcTdbH+4eBwmxPlq3/II5L3siPz3NEgO+q4RE1v", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date1 " = _t, #" Total " = _t, #" INSERT_DATE" = _t]),
#"Extracted Text Before Delimiter" = Table.TransformColumns(Source, {{"Date1 ", each Text.BeforeDelimiter(_, " "), type text}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter", {{"Date1 ", type date}}, "aa-DJ"),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{" Total ", Int64.Type}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{" INSERT_DATE", Text.Trim, type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{" INSERT_DATE", "INSERT_DATE"}, {" Total ", "Total"}, {"Date1 ", "Date1"}}),
#"Changed Type with Locale1" = Table.TransformColumnTypes(#"Renamed Columns", {{"INSERT_DATE", type datetime}}, "en-GB"),
#"Grouped Rows" = Table.Group(#"Changed Type with Locale1", {"Date1"}, {{"AllRows", each _, type table [Date1=nullable date, Total=nullable number, INSERT_DATE=nullable datetime]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "SecondWD", each let
thisdate = Date.EndOfMonth([Date1]) & #time(0,0,0),
thesedates = [AllRows][INSERT_DATE]
in
List.Select(thesedates, each _> thisdate and List.Contains({1,2,3,4,5}, Date.DayOfWeek(_))){1}),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"SecondWD", type datetime}}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Changed Type1", "AllRows", {"Total", "INSERT_DATE"}, {"Total", "INSERT_DATE"}),
#"Added Custom1" = Table.AddColumn(#"Expanded AllRows", "Keep", each if [INSERT_DATE] = [SecondWD] then "Y" else "N"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Keep] = "Y")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"SecondWD", "Keep"})
in
#"Removed Columns"
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat Thanks Pat. But i got another thousand things going in my M (and there are many other columns not just these 3) so i wish not to touch my m query and try to do it in DAX. Anyway you can help with that? Thanks again.
Not sure if you want a measure or a DAX summary table. I recommend you use a measure. Here is one that you can use as a visual-level filter to get your desired result.
Is Second WD =
VAR thisEOM =
EOMONTH ( MIN ( SWD[Date1] ), 0 )
VAR thisInsertDate =
MIN ( SWD[INSERT_DATE] )
VAR vSWD =
MAXX (
TOPN (
2,
CALCULATETABLE (
DISTINCT ( SWD[INSERT_DATE] ),
ALL ( SWD[INSERT_DATE], SWD[Total] ),
SWD[INSERT_DATE] > thisEOM,
WEEKDAY ( SWD[INSERT_DATE] ) IN { 2, 3, 4, 5, 6 }
),
SWD[INSERT_DATE], ASC
),
SWD[INSERT_DATE]
)
VAR result =
IF ( thisInsertDate = vSWD, "Y", "N" )
RETURN
result
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat Thanks a lot. sorry for late reply. but this will work. Cheers. : )
@Anonymous look at the attached file which has a column called Business Day in the Calendar table. You can change it to any business day you want. Change the logic to meet your needs.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k , Thanks Parry. But wish i was good enough to modify my code accordingly. Still learning phase.
Can you help me with specific columns that i have mentioned plz? if possible. I am struglling since morning.
Thanks again @parry2k
@parry2k Thanks to you too. i was able to get to the solution by implementing your suggestion as well.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |