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
Super User
Super User

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
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Solution Authors
Top Kudoed Authors