Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Bob_B
Frequent Visitor

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 based on qty for each branch based on the most recent 3 months.

 

For example, the following data

Data Table  
Branchsource monthqty
SouthA Vendor Jan-226
SouthA Vendor Feb-225
SouthA Vendor Feb-239
SouthA Vendor Apr-233
SouthB VendorNov-232
SouthB VendorJan-248
NorthB VendorMay-226
NorthB VendorAug-233
NorthC Vendor Dec-228
NorthC Vendor Feb-231
NorthB VendorOct-234
NorthB VendorApr-247
EastA Vendor May-233
EastB VendorApr-246
EastA Vendor Jan-237
EastC Vendor Jun-222
EastA Vendor Jan-227
EastC Vendor Sep-234
EastA Vendor Jul-235
EastC Vendor Feb-248

 

Would Pivot to look like:

Bob_B_0-1715353996971.png

If I can create a table from the above that looks like 

Bob_B_1-1715354121979.png

I can then select the most recent 3 months, and the result would look like:

Bob_B_2-1715354216000.png

Any and all help would be appreciated!  Thanks.

 

1 ACCEPTED SOLUTION

@Bob_B ,

 

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

 

xifeng_L_0-1716370746865.png

 

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

 

 

 

 

View solution in original post

11 REPLIES 11
Bob_B
Frequent Visitor

@xifeng_L Thank you -- I'll check this out asap!

Your solutions is great @xifeng_L 

Hi, @Bob_B 

Have you solved your problem? If so, can you share your solution here or mark the correct answer as a standard answer to help other members find it faster. Thank you very much for your kind cooperation!

 

 

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

xifeng_L
Super User
Super User

Hi @Bob_B ,

 

You can use below measure to achieve. 

 

xifeng_L_0-1715357212309.png

 

Primary Source = 
IF(HASONEFILTER('Fact'[Branch]),
    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])
)

 

The key step is to convert the months into a sortable format, such as dates.

 

 

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

 

Thank you~

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",

Bob_B_0-1715801341297.png

I get the following result:

Bob_B_1-1715801405804.png

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!

Hi @Bob_B ,

 

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~

 

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!

Bob_B
Frequent Visitor

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

@Bob_B 

 

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:

 

xifeng_L_1-1716304571358.png

 

 

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:

 

xifeng_L_2-1716304656162.png

 

 

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~

 

 

@xifeng_L : 

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

Bob_B_1-1716318602799.png

In a visual, it looks like this:

 

Bob_B_0-1716318491633.png

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!

 

@Bob_B ,

 

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

 

xifeng_L_0-1716370746865.png

 

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  THANK YOU!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.