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

 

[alansidman]

Show us some sample data 8-10 records using XL2BB. Do not post pictures as we can not manipulate data in pictures.

 

[shansakhi]

Thank you for your email.
Please find the data for your reference. Here I would like to see only 4 latest As of Dates in pivot.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Month	As of Date	Capacity
2	Jan	8-Dec	654,541		Sum of Capacity	As of Date
3	Jan	1-Dec	624,409		Month	8-Dec	1-Dec	24-Nov	17-Nov	Grand Total
4	Jan	24-Nov	651,927		Jan	654541	624409	651927	626109	2556986
5	Jan	17-Nov	626,109		Feb	591093	563715	588339	565605	2308752
6	Jan	10-Nov	603,571		Mar	656568	630145	655954	635932	2578599
7	Feb	8-Dec	591,093		Grand Total	1902202	1818269	1896220	1827646	7444337
8	Feb	1-Dec	563,715
9	Feb	24-Nov	588,339
10	Feb	17-Nov	565,605
11	Feb	10-Nov	533,425
12	Mar	8-Dec	656,568
13	Mar	1-Dec	630,145
14	Mar	24-Nov	655,954
15	Mar	17-Nov	635,932
16	Mar	10-Nov	601,829
17
18
Sheet1

 

[alansidman]

Use Power Query to filter your dates and then pivot the results.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"As of Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([As of Date] <> #date(2022, 11, 10))),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Filtered Rows", {{"As of Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Filtered Rows", {{"As of Date", type text}}, "en-US")[#"As of Date"]), "As of Date", " Capacity")
in
    #"Pivoted Column"

Book5
	A	B	C	D	E	F	G	H	I	J	K
1	Month	As of Date	Capacity
2	Jan	12/8/2022	654541		Sum of Capacity	As of Date
3	Jan	12/1/2022	624409		Month	12/8/2022	12/1/2022	11/24/2022	11/17/2022	Grand Total
4	Jan	11/24/2022	651927		Jan	654541	624409	651927	626109	2556986
5	Jan	11/17/2022	626109		Feb	591093	563715	588339	565605	2308752
6	Jan	11/10/2022	603571		Mar	656568	630145	655954	635932	2578599
7	Feb	12/8/2022	591093		Grand Total	1902202	1818269	1896220	1827646	7444337
8	Feb	12/1/2022	563715
9	Feb	11/24/2022	588339		Month	12/8/2022	12/1/2022	11/24/2022	11/17/2022
10	Feb	11/17/2022	565605		Feb	591093	563715	588339	565605
11	Feb	11/10/2022	533425		Jan	654541	624409	651927	626109
12	Mar	12/8/2022	656568		Mar	656568	630145	655954	635932
13	Mar	12/1/2022	630145
14	Mar	11/24/2022	655954
15	Mar	11/17/2022	635932
16	Mar	11/10/2022	601829
17
Sheet1

 

[Alex Blakenburg]

Given your statement that you "working on a huge data", I agree with Alan that using Power Query would be the way to go.

I think to make it flexible it will need a few more steps than Alan has.

Much as I don't like using the default table name I have assumed your initial data is called "Table1" in the below.

1) Create a new query (blank or just load the data) and give it the name it 01_Data (in the panel on the right of the PQ editor)
The source line of this is the only reference to "Table1" if you need to chang the table name.

a) In the advanced editor. Replace everything your see there with the below:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedTypeDateOnly = Table.TransformColumnTypes(Source,{{"As of Date", type date}})
in
    ChangedTypeDateOnly

b) Close and Load as connection only

2) Create a second blank query (either a blank query or simply duplicate the above) I have called it "02_Last_n_Dates" but the name doesn't matter.
(This references 01_Data)
In the below the number 4 for your last dates appears in the step "FilteredLast_n_Dates"

a) In the advanced editor. Replace everything you see there with the below:

let
    Source = #"01_Data",
    #"Removed Other Columns" = Table.SelectColumns(Source,{"As of Date"}),
    #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"As of Date", Order.Descending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows"),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1, Int64.Type),
    FilteredLast_n_Dates = Table.SelectRows(#"Added Index", each [Index] <= 4),
    MergedQueriesDateWithData = Table.NestedJoin(FilteredLast_n_Dates, {"As of Date"}, #"01_Data", {"As of Date"}, "01_Data", JoinKind.LeftOuter),
    #"Expanded 01_Data" = Table.ExpandTableColumn(MergedQueriesDateWithData, "01_Data", {"Month", " Capacity"}, {"Month", " Capacity"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded 01_Data",{"Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Month", type text}, {" Capacity", Int64.Type}})
in
    #"Changed Type"

b) Close and Load To > pick somewhere on your workbook or let it create a new Sheet.

This will create a new Table with just the last 4 days you can use that for your pivot. You also have the option of continuing on in PQ and pivoting the data there.
There is also the option of putting all your data into the data model.

My output:
(it should appear in your date format when you run it)

20221221 PQ Last 4 Dates shansakhi.xlsm
	L	M	N
2	As of Date	Month	Capacity
3	8/12/2022	Mar	656568
4	8/12/2022	Feb	591093
5	8/12/2022	Jan	654541
6	1/12/2022	Mar	630145
7	1/12/2022	Feb	563715
8	1/12/2022	Jan	624409
9	24/11/2022	Mar	655954
10	24/11/2022	Jan	651927
11	24/11/2022	Feb	588339
12	17/11/2022	Mar	635932
13	17/11/2022	Feb	565605
14	17/11/2022	Jan	626109
Data

 

[shansakhi]

Thank you for your reply.
But I want to use this option of latest 4 dates in pivot.
My data is loaded as connection with pivot.

 

[Alex Blakenburg]

My data is loaded as connection with pivot.

What does this mean ?

 

[shansakhi]

The data is already loaded in Power BI and a pivot is created as connection.
The latest 4 dates I want to show in Pivot only.

 

[Alex Blakenburg]

In that case your best bet is to add a field in Power BI that identifies the last 4 dates and use that as your filter in the Excel Pivot Table.

 

[shansakhi]

Can you please assist with the same. I am not pro in Power BI