Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Summarizing a table to align most recent dates

I've been unable to find/figure out how to summarize a table such that the data is aligned by most recent month, prior month, prior month -1, etc.  My goal is to be able to select the primary source ...
  • xifeng_L's avatar
    xifeng_L
    2 years ago

    Anonymous ,

     

    Sorry, I misunderstood your needs before. You can try below measure.

     

    (I can't very well restore your real date due to date formatting issues, so the results may change, but the logic should be correct.)

     

     

    Measure:

    Primary Source = 
IF(HASONEFILTER('Fact'[Branch]),
    CALCULATE(
        VAR tempTable = SUMMARIZE('Fact','Fact'[source ],"Recent3MonthsTotal",SUM('Fact'[qty]))
        VAR MaxNbr = MAXX(tempTable,[Recent3MonthsTotal])
        RETURN
        MAXX(FILTER(tempTable,[Recent3MonthsTotal]=MaxNbr),'Fact'[source ]),
        TOPN(3,VALUES('Fact'[month]),'Fact'[month])
    )
)

     

     

    Table Expression:

    Table Name = 
ADDCOLUMNS (
    ALL ( 'Fact'[Branch] ),
    "Parimay Source",
        CALCULATE(
            CALCULATE(
                VAR tempTable = SUMMARIZE('Fact','Fact'[source ],"Recent3MonthsTotal",SUM('Fact'[qty]))
                VAR MaxNbr = MAXX(tempTable,[Recent3MonthsTotal])
                RETURN
                MAXX(FILTER(tempTable,[Recent3MonthsTotal]=MaxNbr),'Fact'[source ]),
                TOPN(3,VALUES('Fact'[month]),'Fact'[month])
            )
        )
)

     

     

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Thanks for taking the time to work on this.  This isn't working exactly as expected.  When I add the following additional rows of data (identified as "West",

I get the following result:

you can see that the output is not what you would expect based on the most recent 3 transactions for West.

 

Also, I need to be able to have this data in a table to use in other relationships and export to other users, the Measure is nice but doesn't do everything I need.  Thanks again for your efforts!

xifeng_L's avatar
xifeng_L
Super User
2 years ago

Hi Anonymous ,

 

May I know what the correct result is for the branch "West"?

 

Isn't the calculation logic to calculate the qty of the last three transactions by branch and source, and then take the source with the highest total qty?

 

And, if you want to create a table instead of using a matrix, then you can use the following expression for creating a table:

 

 

Table Name = 
ADDCOLUMNS (
    ALL ( 'Fact'[Branch] ),
    "Parimay Source",
        CALCULATE (
            VAR tempTable =
                ADDCOLUMNS (
                    VALUES ( 'Fact'[source] ),
                    "Recent3MonthsTotal", 
                        CALCULATE ( 
                            SUMX ( 
                                TOPN ( 3, 'Fact', 'Fact'[new month] ), 
                                'Fact'[qty] 
                            ) 
                        )
                )
            VAR MaxNbr =
                MAXX ( tempTable, [Recent3MonthsTotal] )
            RETURN
                MAXX ( FILTER ( tempTable, [Recent3MonthsTotal] = MaxNbr ), 'Fact'[source] )
        )
)

 

 

 

Did I answer your question? If yes, pls mark my post as a solution!

 

Thank you~

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Sorry if I wasn't clear.  The point is to use only the last 3 transactions by date, then determine the primary source.  For West, the primary source would be "D vendor". (I left the initial date col blank since you didn't need that).  Thanks for your help!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      xifeng_L I hope the above clarifies the question.  If not, please let me know.  Thank you!  

      • xifeng_L's avatar
        xifeng_L
        Super User
        2 years ago

        Anonymous 

         

        How to define "last 3 transactions", where month is not empty, and then take the last three?

         

        If yes, you can refer to below measure or table expression.

         

        #1 Measure:

         

         

         

        Primary Source = 
IF(HASONEFILTER('Fact'[Branch]),
    VAR tempTable = 
        ADDCOLUMNS(
            VALUES('Fact'[source ]),
            "Recent3MonthsTotal",
                CALCULATE(
                    SUMX(
                        TOPN(3,'Fact','Fact'[month]),
                        'Fact'[qty]
                    ),
                    'Fact'[month]<>BLANK()
                )
        )
    VAR MaxNbr = MAXX(tempTable,[Recent3MonthsTotal])
    RETURN
    MAXX(FILTER(tempTable,[Recent3MonthsTotal]=MaxNbr),'Fact'[source ])
)

         

         

         

        #2 Table:

         

         

         

        Table Name = 
ADDCOLUMNS (
    ALL ( 'Fact'[Branch] ),
    "Parimay Source",
        CALCULATE (
            VAR tempTable =
                ADDCOLUMNS (
                    VALUES ( 'Fact'[source ] ),
                    "Recent3MonthsTotal", 
                        CALCULATE ( 
                            SUMX ( 
                                TOPN ( 3, 'Fact', 'Fact'[month] ), 
                                'Fact'[qty] 
                            ),
                            'Fact'[month]<>BLANK()
                        )
                )
            VAR MaxNbr =
                MAXX ( tempTable, [Recent3MonthsTotal] )
            RETURN
                MAXX ( FILTER ( tempTable, [Recent3MonthsTotal] = MaxNbr ), 'Fact'[source ] )
        )
)

         

         

         

        Demo - Summarizing a table to align most recent dates.pbix

         

         

        Did I answer your question? If yes, pls mark my post as a solution!

         

        Thank you~