Converting DAX MinX function to Power Query Language

[javirmerino]

Hi Guys, whats the best way to transform this into #M? Apologies if its been answered somewhere already;

NextWorkingDay = 
minx( 
    FILTER(
        ALL(Dim_Date), Dim_Date[IsWeekday] = TRUE  && 
        Dim_Date[IsHolidayUK] = FALSE && 
        Dim_Date[Date] > SELECTEDVALUE('SysAid Extract'[ReceivedDate]) 
    ),Dim_Date[Date])

 i've read that List.Min would be my best option but cannot figure out how to apply the filters also. Any advise would be appreciated.

Thanks in advance!

A

[Jimmy801]

Hello @javirmerino 

a formular like yours are always triggered by a filter or row context and in your case by a specific selectedvalue. So I don't know what's your starting point to do a calculation. However, I've developed some time ago a networkingdays-function for m that accepts a starting, end date and a list with holiday. You could try to apply this 

// fnNetWorkDays2
(DateStart as date, DateEnd as date, optional Holidays as list) =>
let
    Switch = DateStart > DateEnd,
    StartDate = if Switch then DateEnd else DateStart,
    EndDate = if Switch then DateStart else DateEnd,
    HolidayIntern = if Holidays = null then List.Buffer({}) else List.Buffer(Holidays),
    CreateListDates =List.Buffer( List.Dates
    (
        StartDate,
        Duration.TotalDays
        (
            EndDate - StartDate
        )
        +1,
        #duration(1,0,0,0)
    )),

    ExcludeWeekend = List.Select
    (
        CreateListDates,
        each 
        Date.DayOfWeek
        (
            _,
            Day.Monday
        )
        <5
    ),

    ExcludeHoliday = 
    if Holidays = null then ExcludeWeekend else List.Difference
    (
        ExcludeWeekend,
        HolidayIntern
    ),
    Result = List.Count
    (
        ExcludeHoliday
    ),

    FinalResult = try (if Switch then -1 else 1) * (Result) otherwise null
    
in
    FinalResult

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[javirmerino]

Thanks, @Jimmy801.

Would you mind talking me through what each part of the query is doing, please? i can sort of work out some of it but i've never seen or used the SWITCH function, for example. 

And just for clarity, i'm trying to determine NEXT working day rather than NET working days as your formula calculates. ie. If the ReceivedDate is a Saturday then the next working day would be the following Monday (subject to Bank Holidays).

[Jimmy801]

Hello @javirmerino 

could you then please describe how is made your database in Power Query? What databases you have and how you need to calculate it

Jimmy

[javirmerino]

Apologies, @Jimmy801, i thought i replied to this hours ago and had been waiting for a response. Thank you for your patience!

The database is constructed as below, with a direct query to a SysAid SQL Server, linked to a number of dimension tables;

i'm trying to calculate a DueDate, based on the ReceivedDate and the Urgency fields in the extract.

First i need to calculate the NextWorkingDay; if the request is raised outside of office hours then the calculated StartTime should be 9am the next working day. Ie. if a request is raised on Saturday 1st February then the starting time and date should be Monday 3rd February at 09:00am.

Secondly, i need to determine the hours from the SLA table to add to the calculated start time and date to give a DueDate. if the Urgency is "Urgent" then there is an requirement to complete within 4 working hours. Again, the SLA is based on working hours so an Urgent incident raised on Friday 31st January at 16:59pm would not be due until Monday 3rd February at 12:59pm.

Finally, i need a column to determine if the request was completed within the agreed SLA. if the CompletedDate is greater then the DueDate then Completed Late, else its Completed On Time.

[Jimmy801]

Hello @javirmerino 

you have asked to a solution in Power Query, but the data is already in your datamodel and there is no way to read your datamodel from Power Query. So I don't know how i could help you with Power Query in this case.

So I would see your request in DAX with a new column for SysAid Extract, or what is the goal to do this in Power Query?

Jimmy

[javirmerino]

Thank @Jimmy801. I have amended my response slightly to prevent further confusion. I have instead amended my desired calculated column to DueDate to prevent us getting mixed up.

The RequiredCompletionDate column within my dataset is a null field. It requires the end user to populate this with a date normally so is null in 99% of records. see extract below;

ID	Type	Sub Type	Status	Urgency	Required Completion Date	Received	ReceivedDate	ReceivedTime	Closed	ClosedDate	ClosedTime
67674	Maintenance	Visit Amendment	Closed	Urgent	null	09/09/2019 15:05:52	09/09/2019	15:05:52	2019-09-10 10:59:48	10/09/2019	10:59:48
67847	Maintenance	Field Team Change	Closed	Urgent	null	11/09/2019 15:00:56	11/09/2019	15:00:56	2019-09-11 15:54:22	11/09/2019	15:54:22
68287	Maintenance	Site Contract Activate	Closed	Very High	null	19/09/2019 13:52:27	19/09/2019	13:52:27	2019-09-20 13:18:00	20/09/2019	13:18:00
68448	Maintenance	Field Team Change	Closed	Urgent	null	23/09/2019 14:52:10	23/09/2019	14:52:10	2019-09-24 09:34:12	24/09/2019	09:34:12

My solution is to automate this DueDate based on parameters that are already provided on every incident. I'd like it calculated within Power Query (a preference not a requirement) but if the solution can be done in DAX i'd happily use that, too.

Thnaks for sticking with this, Jimmy. I appreciate that new users can be frustrating!

[Jimmy801 · Accepted solution]

Hello @javirmerino 

I think this should be all be done in DAX, as your data is already in your datamodel and you have to handel big data volumes.

The first formula you already posted yourself by determine the next working day. There would only be the need to add a time-value. Your second request is about adding hours from another table. As they already connected this daat could be retrieved by a formula like Related(TblSLA[Hours])

Hope I could at least help a little bit

Jimmy

[javirmerino]

Hi @Jimmy801, thanks for your patience here. i really appreciate it.

I have a direct query to a SQL server named as SysAid Extract with a many to one relationship to a date dimension table and an SLA dimension table.

i'm trying to calculate a Due Date for each sysaid incident based on their urgency.

The first calculated column i need is to determine the [NextWorkingDay] based on the [ReceivedDate] within the extract. If a request is received out of normal office hours then the [StartDate] should be the next working day, excluding holidays and weekends. If the request is received within normal office hours then i can just use the provided [ReceivedDate]. 

The next calculation should use this calculated [StartDate], and add x working hours based on the urgency and the hours in TblKPI. For example, if an urgent request was received at 16:59 on Friday 31st January with the SLA for Urgent queries being completion within 4 hours. The [NextWorkingDay] would be Monday 3rd February, so the [DueDate] should be Monday 3rd February and the [DueTime] should be 12:59.

I'm assuming that this should all be calculated in Power Query (i read somewhere that "if you can do it in DAX or M, then do it in M" is a good working practice) but if its easier and quicker to calculate these as measures in DAX then i'm happy to use that process instead. You're the expert here, Jimmy!