ExcelAtEverything
Active Member
- Joined
- Jan 30, 2021
- Messages
- 351
- Office Version
- 2019
- Platform
- 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!
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!