Changing GETPIVOTDATA to add all sales in a month up to the date shown in a cell

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
351
Office Version
  1. 2019
Platform
  1. Windows
Hello,

My project is a yearly/monthly/daily sales tracker. All of my data is located in Excel sheets kept in different folders which are all kept on my desktop, and the following question is based off of info from 1 of those folders named "Monthly Orders Reports". The info is accessed by my spreadsheet by way of Power Query > Data Model > Pivot Tables.
I recently changed my project so that instead of using =today() for the report date, I'm now using a manual-entry date (so that I can go back in time whenever I want, and look at the (yesterday, MTD, & YTD) numbers thru the lens of whatever date is entered into $G$3 of the main sheet. The problem in doing so however, is that now when I plug in a reporting date of March 29, 2021 (for example), my month to date number needs to count all daily sales totals for March, up to the prior day of the reporting date. So since my reporting date now says March 29th, the cell needs to show sales from March 1 to March 28th (and not include the 29th, because setting the report date as 3/29/21 basically assumes that it is now the morning of 3/29/21, & we want to see all sales MTD thru the close of business the night before). Before when the reporting date just used =today(), I just used the sales report's monthy subtotals by store as they are always up to today by default. So I need some way to sum the sales totals for the respective dates, according to the the user plugs in to $G$3.

Here is what I have so far (this is the formula in G6), which is incorrectly displaying the sum of the entire month of March instead of 3/1 to 3/28:
=GETPIVOTDATA("[Measures].[Sum of Line Item Total]",'YTD GS'!$B$3,
"[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[Org #].&[" &[@[Org '#]]& "]",
"[Monthly Orders Reports].[Date (Month)]","[Monthly Orders Reports].[Date (Month)].&[" & TEXT($G$3,"mmm") & "]",
"[Monthly Orders Reports].[Date (Year)]","[Monthly Orders Reports].[Date (Year)].&"&"["&YEAR($G$3)&"]")


Here is a snap of the MTD GS column located on the "Main" tab, followed by a snap of the Pivot Table which is located on "YTD GS" tab.
Thanks!

1617760313313.png
1617760727401.png
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top