cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PrajwalR
Regular Visitor

Groupby function.

Hi all,
I have Column "Ageing" with numerical value which indicates the no. of days stocks are present in out database. 
EX: Ageing

       1012

       1028

       500

       450

       90

       1500
Like this. I want to group the ageing days like 0-90days, 180-270 days, 270-365 days, >1 year, >2 years, etc
Please help me with a DAX function which will sort these accordingly.
Thank you.
Prajwal R

1 REPLY 1
jennratten
Responsive Resident
Responsive Resident

Hello - here is an example of how you can do this with Power Query (M).

 

Table Name: SampleTable

jennratten_1-1633899621315.png

 

SampleTable Script

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZYzBDcAgDAN34Y0U2xFdBrH/Gg0FJKL+zj7LvRcaTRBLDdTCUVOvg7N3Y4sEhHhM7WeE78p5f/l9tlfT7ERkM14=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [startDate = _t, endDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"startDate", type date}, {"endDate", type date}})
in
    #"Changed Type"

 

Desired Output: Add two columns to the SampleTable - one for the number of months elapsed between the two dates and another for a custom label for the number of months.

 

Step 1: Create a new table with your mapping options.  This should contain two columns, one for the number of months and one for the label that should be assigned.  (You could easily do something similar for days instead of months).

 

Table Name: CustomDateRangeDim

jennratten_0-1633899471222.png

 

CustomDateRangeDim Script

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdI7CgIxFEbhrYS0jnBz/3kuwgVITDGoYKXgo5jdGxxBi9N+3YGTc7TYxMPLTMcgC6d5ecTS5Jgqp7ANCrvb9XlZ0QlF2FZUxeT/2qH2qAPqiDqhJmNOzM78y9uf5/sXW8KOsCccCEfCCdCNMBE6IRU5FTkVORU5FTkVORWJikRFoiJRkahIVCQq0vrfB8ImlvIG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MinMonths = _t, Label = _t]),
    SetTypes = Table.TransformColumnTypes(Source,{{"MinMonths", Int64.Type}})
in
    SetTypes

 

Step 2: Add a new blank query for a custom function.

Function Name: fnDateDifferenceInMonths

Function Script

// ****************************************************************************************************************/
//   PURPOSE
//   - Calculate the difference between two dates in months
// ****************************************************************************************************************/

let
    fn = ( DataTable as table, StartDateColumn as text, EndDateColumn as text, NewColumnName as text ) as table =>

    let
        
        date_diff = Table.AddColumn(
                 // starting table
                DataTable,
                // name of new column to be created
                NewColumnName, 
                each 
                    // declare inline variables
                    let 
                        // calculate the compelete years elapsed
                        DateDiffInYears = 
                            Date.Year ( Record.Field ( _, EndDateColumn ) ) - Date.Year ( Record.Field ( _, StartDateColumn ) ),
                        // calculate the remaining months
                        MonthsRemaining = 
                            Date.Month ( Record.Field ( _, EndDateColumn ) ) - Date.Month ( Record.Field ( _, StartDateColumn ) ),
                        // calculate the total number of months
                        DateDiffInMonths = 
                            ( DateDiffInYears * 12 ) + MonthsRemaining
                    in
                        DateDiffInMonths,
                        Int64.Type
            )
    in
        date_diff
in
    fn

 

Step 3: Invoke the function as a new query (or you could do this within the same query). 

 

Table Name: Result

jennratten_3-1633900113913.png

 

Result Script

let
    Source = fnDateDifferenceInMonths(SampleTable, "startDate", "endDate", "DateDiffInMonths"),
    LowestMinMonthsInDim = List.Min(CustomDateRangeDim[MinMonths]),
    HighestMinMonthsInDim = List.Max(CustomDateRangeDim[MinMonths]),
    NormalizeMonths = Table.ReplaceValue(
        Source, 
        each [DateDiffInMonths] , 
        each 
            // if the months elapsed is less than the lowest min in the dim table
            if [DateDiffInMonths] < LowestMinMonthsInDim 
            // replace with the lowest min
            then LowestMinMonthsInDim
            else 
                // if the months elapsed is greater than the highest min in the dim table
                if [DateDiffInMonths] > HighestMinMonthsInDim then 
                // replace with the highest min
                HighestMinMonthsInDim 
                // otherwise perform no replacement
                else [DateDiffInMonths],
                Replacer.ReplaceValue,
                {"DateDiffInMonths"}
    ),
    // merge with the custom date dim
    merge = Table.NestedJoin(NormalizeMonths, {"DateDiffInMonths"}, CustomDateRangeDim, {"MinMonths"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(merge, "Table", {"Label"})
in
    #"Expanded Table"

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors