cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 Branch source month qty South A Vendor Jan-22 6 South A Vendor Feb-22 5 South A Vendor Feb-23 9 South A Vendor Apr-23 3 South B Vendor Nov-23 2 South B Vendor Jan-24 8 North B Vendor May-22 6 North B Vendor Aug-23 3 North C Vendor Dec-22 8 North C Vendor Feb-23 1 North B Vendor Oct-23 4 North B Vendor Apr-24 7 East A Vendor May-23 3 East B Vendor Apr-24 6 East A Vendor Jan-23 7 East C Vendor Jun-22 2 East A Vendor Jan-22 7 East C Vendor Sep-23 4 East A Vendor Jul-23 5 East C Vendor Feb-24 8

Would Pivot to look like:

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

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

Any and all help would be appreciated!  Thanks.

1 ACCEPTED SOLUTION
Solution Sage

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

11 REPLIES 11
Frequent Visitor

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

Community Support

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!

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

Solution Sage

Hi @Bob_B ,

You can use below measure to achieve.

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

Frequent Visitor

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!

Solution Sage

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 =
ALL ( 'Fact'[Branch] ),
"Parimay Source",
CALCULATE (
VAR tempTable =
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~

Frequent Visitor

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!

Frequent Visitor

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

Solution Sage

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 =
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 =
ALL ( 'Fact'[Branch] ),
"Parimay Source",
CALCULATE (
VAR tempTable =
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~

Frequent Visitor

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!

Solution Sage

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

Frequent Visitor

@xifeng_L  THANK YOU!