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])
            )
        )
)

     

     

     

     

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~

 

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

xifeng_L : 

 I updated the date information in the Fact table to reflect actual dates as you originally suggested:

In a visual, it looks like this:

 

From the above table, you can see that for the latest 3 transactions:

East (5 - A, 6 - B and 8 - C) yields C vendor

North ( 7+4 + 3= 14) yields B vendor 

South ( (8+2)= 10 B, 3 - A) yields B vendor

West  (1 + 1 = 2 for D, 1 for C) yields D vendor

 

I'll work on how to add a link to my updated model, but you get the idea.  It looks like maybe you are taking the most recent 3 transactions for each vendor, then doing the comparison?  The idea is to find the vendor that is currently (based on the latest 3 transactions) providing qty, not the one that provided the most qty over the history.  Thanks!

 

  • xifeng_L's avatar
    xifeng_L
    Super User
    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

    xifeng_L  THANK YOU!