Advanced sorting/calculation

[JoMo1]

Dear Power BI lovers,

I have one kind of tricky problem to solve.

Until today I have used Excel in combination with Power Query to solve bussines problems and creating some Reports. But since new times are coming there are initiative to replicate all in PBI.

As those reports are already well prepared and running well in Excel I try to do the same thing in PBI, but i'm stuck.

As you can see in the snapshoot below there are calculation which represent Days since Last Billing date, which is in fact number of days from previous End Billing date up to new Start billing date, only thing is that both Client and Product nums should be the same if they are not we do not calculate it.

As this is really complicated to replicate in PBI and im simple run of ideas how to solve it, I would need your help to tell me is this even possible in PBI, and if it is how, either through DAX or C Colums

[v-yingjl · Accepted solution]

Hi @JoMo1 ,

You need to create a custom function to calculate the working days in Power Query first, called 'Networkdays'

(StartDate as date, EndDate as date) as number =>
let
    DateList = List.Dates(StartDate,Number.From(EndDate - StartDate),#duration(1,0,0,0)),
    RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_,Day.Monday) < 5),
    CountDays = List.Count(RemoveWeekends) 
in
    CountDays

Then the data source query would be like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fctBCsAgDETRu7i2jBkl6inavXj/a1SEUrPJKh/yZoxwP5dqu5h6iEEoaR0mCipI0zM6uoPFtKuFyKYdvd6QatrVBWymDy2at6bkjxD/dLfPC/TMOV8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Client Code" = _t, #"Product Num" = _t, #"Start Billing Date" = _t, #"End Billing Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client Code", type text}, {"Product Num", Int64.Type}, {"Start Billing Date", type date}, {"End Billing Date", type date}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", {"Client Code", "Product Num"}, 
            {
                {"Data", each 
                let tab = Table.AddIndexColumn(_,"Index",1,1)
                in Table.AddColumn(
                    tab,"Previous Row",
                    (x)=> try Table.Max(Table.SelectRows(tab,(y)=>y[Index]=x[Index]-1),"Index")[End Billing Date]
                                    otherwise null 
                ), 
                type table [Client Code=nullable text, Product Num=nullable number, Start Billing Date=nullable date, End Billing Date=nullable date, Index = nullable number, Previous Row=nullable date]
                }
            }
        ),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Start Billing Date", "End Billing Date", "Previous Row"}, {"Start Billing Date", "End Billing Date", "Previous Row"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Expanded Data", "Days since Last Billing", each try Networkdays([Previous Row], [Start Billing Date]) otherwise null,type date),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Previous Row"})
in
    #"Removed Columns"

You can get the expected result now:

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JoMo1]

Dear @v-yingjl and @ronrsnfld ,

Thank you for your quick responses, this is awesome!

It is working as it should, many thanks @v-yingjl, you save me a lot of hours searching for the solution online!

Kind regards,

JoMo1

[ronrsnfld]

You can add that column using Power query (which is also used in the Transform tab in PBI.

 - To ensure that Client Code and Product name are the same, in PQ you would "Group By" those two columns

 - Then add a column to each subtable, where that column is a shifted down "Start Date" column

 - Re-expand the tables

 - Then you can see that if the shifted date column contains a null, don't calculate anything, but if it does not, do your subtraction.

You'll have to write your own NetWorkDays function (I don't believe there is one in DAX either).  Paste the code below into a blank query, and rename it "fnNetWorkDays"

Note there is an optional holidays parameter.

//rename this "fnNetWorkDays"

(start as date, end as date, optional holidays as list)=>
let 
    allDates = List.Dates(start,Duration.Days(end-start)+1, #duration(1,0,0,0)),
    hDays = if holidays=null then {} else holidays,
    remWeekEnds = List.Select(allDates, each Date.DayOfWeek(_) <> Day.Saturday and Date.DayOfWeek(_) <> Day.Sunday),
    remHolidays = List.Difference(remWeekEnds,hDays)
in List.Count(remHolidays)

 Then you can use this code for your main query:

let

//Change table name (and method if necessary) of next line in accordance with your actual setup
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Client Code", type text}, {"Product Name", Int64.Type}, 
        {"Start Billing Date", type date}, {"End Billing Date", type date}}),

//group by client code and product name
//  then add shifted start column to each subtable
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Client Code", "Product Name"}, {
        {"Shifted start Billing Date", (t)=> Table.FromColumns(Table.ToColumns(t) & 
            {{null} & List.RemoveLastN(t[Start Billing Date],1)}
            )}}),
    #"Expand Shifted List" = Table.ExpandTableColumn(#"Grouped Rows","Shifted start Billing Date",
       List.RemoveFirstN(Table.ColumnNames(#"Grouped Rows"[Shifted start Billing Date]{0}),2),
       List.RemoveFirstN(Table.ColumnNames(Source),2) & {"Shifted Start"}),
    #"Type Expanded List" = Table.TransformColumnTypes(#"Expand Shifted List",
    {
        {"Client Code", type text}, {"Product Name", Int64.Type}, 
        {"Start Billing Date", type date}, {"End Billing Date", type date},
        {"Shifted Start", type date}}),

//add your net workdays column
    #"Added Custom" = Table.AddColumn(#"Type Expanded List", "Business Days since Last Billing", 
        each if [Shifted Start]=null then null else fnNetWorkDays([Shifted Start],[End Billing Date])-1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Shifted Start"})
    
in
    #"Removed Columns"