How do I know the last selected date in Pivot Table? ....

[Nasmin Saheed]

Hi Dear Friends,

I need know the below can be done in Excel or not

Every day I am using excel to key in some figures date wise and the same sheet I have made pivot table as well( around 5 pivot table) to summarize the figures.

After updating figure in the excel before refresh pivot table I need to select the date and refresh, than only my pivot will update to current date

Here I need to know how to check the date filter in pivot whether I have already selected the current date or not, because if I select more than 1 date pivot will show as (Multiple Items) but it will not show last selected date

My question is here I need to know the date which I have selected {where it is hidden (Multiple Items)} and if possible it should show in the next cell
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 64"]Date[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 135"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 135"](Multiple Items)[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 148"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 148"]In this cell I need to know the last selected date[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 64"]Row Labels[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 135"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 135"]Sum of Sale-15[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 148"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 148"]Sum of GP[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 152"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 152"]Sum of Retail-Target[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
[TR]
[TD][TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 64, align: right"]1000[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 135"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 135"]4,690,000[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 148"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 148"]2,249,400[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 152"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 152"]4,700,000[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
[TR]
[TD][TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 64, align: right"]2000[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 135"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 135"]3,550,000[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 148"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 148"]1,696,900[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 152"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 152"]3,500,000[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
[TR]
[TD][TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 64"]Grand Total[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 135"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 135"]8,240,000[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 148"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 148"]3,946,300[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 152"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 152"]8,200,000

[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[ranman256]

worksheet("Pivot").select
range("B4").select
Selection.End(xlToRight).Select
msgbox activecell.value,,"Last Date"

 

[TimRodman]

You might also consider using Power Query to always dynamically filter on the current date when you refresh so you don't have to manually change the filter every time.

 

[Nasmin Saheed]

Hi ranman256,

Thanks you for the reply on my concern, I did not get exactly what is this code also how to use it, if you don’t mind can i have your personal mail ID so I can send the file and can explain to better way.

 

[ranman256]

You'd put the code in a macro, run the macro and it tells you the last date in the pivot

 

[TimRodman]

If you have an Excel table like the one pictured in blue below and want a Power Query output like the table in pictured in green below:

Then you could use this M code in Power Query:

let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Column", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date] = DateTime.Date(DateTime.LocalNow())))
in
    #"Filtered Rows"

 

[ranman256]

range("B3").select
Selection.End(xlDown).Select
msgbox "Last date =" & activecell.value

 

[Nasmin Saheed]

Hi Ranman,

i have tried your code and it is giving me error, as discuses can you plz check your yahoo mail ID

 

[Nasmin Saheed]

Hi Tim Rodman,

you workout is looking nice exactly i need the same but i dont know how to use Power Query:

 

[TimRodman]

Ranman's code is VBA. My code is Power Query.

If you want to use the Power Query code, just do the following:

1. Download and Install Power Query (click here)


2. Open Excel and follow these clicks:

3. In the Power Query window, follow these clicks:

4. Replace the default code with my code (below) and change "Table1", "Date", and "Column" to match whatever information you have in Excel:

let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Column", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date] = DateTime.Date(DateTime.LocalNow())))
in
    #"Filtered Rows"