Forum Discussion

anupampandey's avatar
anupampandey
Helper III
9 years ago
Solved

Month MAX record

Hi,

 

I need to get the date on which MAX transaction happened during the each month. Till now I am unable to do so. 

Example:

Tran_DTTransactionCount
23-02-201624
24-02-201639
25-02-201679
26-02-201696
27-02-2016133
28-02-2016141
29-02-201692
01-03-201678
02-03-201671
03-03-2016117
04-03-2016132
10-03-2016266
11-03-2016194
12-03-2016165
13-03-2016109
14-03-2016253
12-04-2016437
13-04-2016397
14-04-2016318
15-04-2016260

 

Desired result:

Tran_DTTransactionCount
28-02-2016141
10-03-2016266
12-04-2016437

 

Please help me on it.

 

 

Thanks & Regards,

Anupam

  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    An alternative in - advanced - Power Query would be:

     

    let
        Source = TransactionTable,
        Grouped = Table.Group(Source, {"Tran_DT"}, {{"AllData", each Table.MaxN(_,"TransactionCount",1), Value.Type(Source)}},null,
        (x,y) => 
        let
            Sort1 = Value.Compare(Date.Year(x[Tran_DT]),Date.Year(y[Tran_DT])),
            Sort2 = if Sort1 <> 0 then Sort1 else Value.Compare(Date.Month(x[Tran_DT]),Date.Month(y[Tran_DT]))
        in
            Sort2),
        Removed = Table.RemoveColumns(Grouped,{"Tran_DT"}),
        Expanded = Table.ExpandTableColumn(Removed, "AllData", {"Tran_DT", "TransactionCount"}, {"Tran_DT", "TransactionCount"})
    in
        Expanded

    Basically it is "Group By" Tran_DT, but taking full advantage of Table.Group capacities;

     

     

    Syntax of Table.Group:

    Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number, optional comparer as function) as table

    A custom comparer function is provided to group on year/month (the inner "let .. in" part).

     

    Parameter aggregatedColumns is adjusted so a table is returned with 1 record (the maximum TransactionCount) and with the same columntypes as table Source (this is the part Value.Type(Source)).

     

    Note that the column "Tran_DT" after grouping does not contain the date on which the maximum was reached, so this column is removed.

    The nested tables in column "AllData" contain exactly the information you require, so after expanding the nested tables, you have your result.

14 Replies

      • vanessafvg's avatar
        vanessafvg
        Community Champion

        ok so if your date table is mapped from date to transaction date 

         

        then something like this should work

         

        measure = CALCULATE('Table'[TransactionCount], LASTDATE(Dates[Date]))

         

        then drag your year / month on to the visual you want to see and it should bring back the data for the last day