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
Anonymous
Not applicable

How to get the 2nd Business Day of the next month?

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


1 ACCEPTED 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] ), )
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

 

 

mahoneypat_0-1628078401681.png

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

9 REPLIES 9
v-kkf-msft
Community Support
Community Support

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

mahoneypat
Employee
Employee

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"

 

mahoneypat_0-1628043053466.png

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

@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] ), )
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

 

 

mahoneypat_0-1628078401681.png

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

@mahoneypat Thanks a lot. sorry for late reply. but this will work. Cheers. : )

parry2k
Super User
Super User

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

Anonymous
Not applicable

@parry2k , Thanks Parry. But wish i was good enough to modify my code accordingly. Still learning phase.

Anonymous
Not applicable

Can you help me with specific columns that i have mentioned plz? if possible. I am struglling since morning. 

Thanks again @parry2k 

Anonymous
Not applicable

@parry2k Thanks to you too. i was able to get to the solution by implementing your suggestion as well. 

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.