Auto select latest 4 dates in Pivot - Column Filter

[shansakhi]

Hello Everybody,
I am working on a huge data which has multiple dates and I want to display data only for latest 4 dates.
How can I Auto select latest 4 dates in Pivot - Column Filter.

Regards,
Shan

 

[Alex Blakenburg]

I think you will be better off starting a new thread under the Forum category > Power Tools and make it clear you need Power BI expertise.
Also do you have control over the Power BI application ie can you make changes to it.
In Power BI you could probably achieve what you want using either Power Query or DAX but you need someone who has done it before to guide you.

 

[shansakhi]

Sure. Thank you.

 

[jdellasala]

As much as I love Power Query, sometimes there's an easier way to get the information needed. Of course, that depends on the information needed is communicated clearly. "I want to display data only for latest 4 dates" when the data provided has three date values the same. So I'm assuming that what's needed is the TOTAL (or average?) for the last 4 largest dates. That means determining how many items are part of the "largest date" group, and so on through the second, third, and fourth groups. Like this:

Book1
	A	B	C	D	E	F	G	H	I
1	Month	As of Date	Capacity		Last 4 Dates	Total Capacity		Next Rank	# of Matches
2	Jan	08-12-22	654,541		08-12-22	1,902,202		1	3
3	Jan	01-12-22	624,409		01-12-22	1,818,269		4	3
4	Jan	24-11-22	651,927		24-11-22	1,896,220		7	3
5	Jan	17-11-22	626,109		17-11-22	1,827,646		10	3
6	Jan	10-11-22	603,571
7	Feb	08-12-22	591,093
8	Feb	01-12-22	563,715
9	Feb	24-11-22	588,339
10	Feb	17-11-22	565,605
11	Feb	10-11-22	533,425
12	Mar	08-12-22	656,568
13	Mar	01-12-22	630,145
14	Mar	24-11-22	655,954
15	Mar	17-11-22	635,932
16	Mar	10-11-22	601,829
Sheet6

Cell Formulas
Range	Formula
E2:E5	E2	=LARGE($B$2:$B$16,H2)
F2:F5	F2	=SUMIFS($C$2:$C$16,$B$2:$B$16,E2)
I2:I5	I2	=COUNTIFS(B2:B16,E2)
H3	H3	=H2+I2
H4:H5	H4	=I3+H3

This is a technique I saw on a YouTube Video just yesterday, and thought it might be relevant here. Even if it's not, it's an interesting technique!