# Auto select latest 4 dates in Pivot - Column Filter



## shansakhi (Dec 18, 2022)

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 (Dec 19, 2022)

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


----------



## shansakhi (Dec 20, 2022)

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.

Book1ABCDEFGHIJK1MonthAs of Date Capacity2Jan8-Dec654,541Sum of  CapacityAs of Date3Jan1-Dec624,409Month8-Dec1-Dec24-Nov17-NovGrand Total4Jan24-Nov651,927Jan65454162440965192762610925569865Jan17-Nov626,109Feb59109356371558833956560523087526Jan10-Nov603,571Mar65656863014565595463593225785997Feb8-Dec591,093Grand Total190220218182691896220182764674443378Feb1-Dec563,7159Feb24-Nov588,33910Feb17-Nov565,60511Feb10-Nov533,42512Mar8-Dec656,56813Mar1-Dec630,14514Mar24-Nov655,95415Mar17-Nov635,93216Mar10-Nov601,8291718Sheet1


----------



## alansidman (Dec 20, 2022)

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

Book5ABCDEFGHIJK1MonthAs of Date Capacity2Jan12/8/2022654541Sum of  CapacityAs of Date3Jan12/1/2022624409Month12/8/202212/1/202211/24/202211/17/2022Grand Total4Jan11/24/2022651927Jan65454162440965192762610925569865Jan11/17/2022626109Feb59109356371558833956560523087526Jan11/10/2022603571Mar65656863014565595463593225785997Feb12/8/2022591093Grand Total190220218182691896220182764674443378Feb12/1/20225637159Feb11/24/2022588339Month12/8/202212/1/202211/24/202211/17/202210Feb11/17/2022565605Feb59109356371558833956560511Feb11/10/2022533425Jan65454162440965192762610912Mar12/8/2022656568Mar65656863014565595463593213Mar12/1/202263014514Mar11/24/202265595415Mar11/17/202263593216Mar11/10/202260182917Sheet1


----------



## Alex Blakenburg (Dec 20, 2022)

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.xlsmLMN2As of DateMonth Capacity38/12/2022Mar65656848/12/2022Feb59109358/12/2022Jan65454161/12/2022Mar63014571/12/2022Feb56371581/12/2022Jan624409924/11/2022Mar6559541024/11/2022Jan6519271124/11/2022Feb5883391217/11/2022Mar6359321317/11/2022Feb5656051417/11/2022Jan626109Data


----------



## shansakhi (Dec 21, 2022)

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 (Dec 21, 2022)

shansakhi said:


> My data is loaded as connection with pivot.


What does this mean ?


----------



## shansakhi (Dec 21, 2022)

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 (Dec 22, 2022)

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 (Dec 22, 2022)

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


----------



## shansakhi (Dec 18, 2022)

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 (Dec 22, 2022)

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 (Dec 22, 2022)

Sure. Thank you.


----------



## jdellasala (Dec 22, 2022)

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:
Book1ABCDEFGHI1MonthAs of Date CapacityLast 4 DatesTotal CapacityNext Rank# of Matches2Jan08-12-22654,54108-12-221,902,202133Jan01-12-22624,40901-12-221,818,269434Jan24-11-22651,92724-11-221,896,220735Jan17-11-22626,10917-11-221,827,6461036Jan10-11-22603,5717Feb08-12-22591,0938Feb01-12-22563,7159Feb24-11-22588,33910Feb17-11-22565,60511Feb10-11-22533,42512Mar08-12-22656,56813Mar01-12-22630,14514Mar24-11-22655,95415Mar17-11-22635,93216Mar10-11-22601,829Sheet6Cell FormulasRangeFormulaE2:E5E2=LARGE($B$2:$B$16,H2)F2:F5F2=SUMIFS($C$2:$C$16,$B$2:$B$16,E2)I2:I5I2=COUNTIFS(B2:B16,E2)H3H3=H2+I2H4:H5H4=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!


----------

