Month to date, last month to date, last year to date

[gazmoz17]

Hi,

I wish to create a current month report.
KPI: Sales, Profit

So a snap shot of how were doing this month compared to last month and last years month all to dates. So in Sept now…so Sept 23 mtd, Aug 23 mtd and Sept 22 mtd.

Is this possible based on vba populating a cell with todays date. Have this cell as a date parameter to automatically bring relevant info in as workbook open event. Would I have a pivot tables for those 3 dates and the pivots are linked to 3 seperate power queries filtered by the cell parameter?

Not sure how to do it. Currently I just have a “Source query” which is all 2023 sales data. Can access 2022 data. But I want it to be dynamic so this time next year it runs itself upon opening the workbook. Can you tell Power query to look back 12 months of data from todays date (parameter cell). I dont want my “Soruce query” too big with historical data I dont need.

Any advisal much appreciated.

Thanks
Gareth

 

[awoohaw]

Hi,

I wish to create a current month report.
KPI: Sales, Profit

So a snap shot of how were doing this month compared to last month and last years month all to dates. So in Sept now…so Sept 23 mtd, Aug 23 mtd and Sept 22 mtd.

Is this possible based on vba populating a cell with todays date. Have this cell as a date parameter to automatically bring relevant info in as workbook open event. Would I have a pivot tables for those 3 dates and the pivots are linked to 3 seperate power queries filtered by the cell parameter?

Not sure how to do it. Currently I just have a “Source query” which is all 2023 sales data. Can access 2022 data. But I want it to be dynamic so this time next year it runs itself upon opening the workbook. Can you tell Power query to look back 12 months of data from todays date (parameter cell). I dont want my “Soruce query” too big with historical data I dont need.

Any advisal much appreciated.

Thanks
Gareth

I think you should post this in the Power Tools forum.

But also, Mr. excel has a tool called xl2bb where you can post miniworkbooks of your data and formulas. Please post your 2023 and 2022 anonymized sample data so the forum can help you. If you can't use the tool, then please post your data in Tables.

Thanks in advance.

 

[gazmoz17]

I think you should post this in the Power Tools forum.

But also, Mr. excel has a tool called xl2bb where you can post miniworkbooks of your data and formulas. Please post your 2023 and 2022 anonymized sample data so the forum can help you. If you can't use the tool, then please post your data in Tables.

Thanks in advance.

Hi, thats great…thanks for this will do much appreciated.