Retreive Month-over-Month and Last Year-over-Current Month Present Year reports

[RuslanF]

Hi there, 

I need to retreive two different reports based on average sales prices for the past and present year. One is with month-over-month average prices for both past and present year, e.g. based on 5 months average price of last year and same period of present year. Another one with fixed average sales prices for the 12 months of the last year and moving average sales prices for several months of the present year. The desired results of both options are on the right side tables of the attached file. I only could retreive them by manually changing the filters for both pivot tables on the left side month by month. The upper pivot table is made with Power Pivot with measures, the lower pivot table is made with standard pivot table tool. Would be glad if you help me.

The sample file is available here: https://www.dropbox.com/scl/fi/56juil4oow8t36go6l7um/SampleData.xlsx?rlkey=07s7sgxgbn6yi3yqa2hhl15v4...

Thank you for your input.

[Anonymous]

Hi @RuslanF ,

Have you resolved the issue now? If so, please share the answer and mark your answer as the solution.

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RuslanF]

Hi, 

no, unfortunately I have no solution till now. I shared the file and some details below, but got no response.

Would be glad of any help from the community.

Thanks.

[Anonymous]

Hi @RuslanF ,

 I can't get your share for privacy reasons.

Please have a try.

To create the first report, you can use a combination of the DATESYTD and AVERAGE functions in Power Pivot.

con==AVERAGE(DATESYTD('Sales'[Date]))

To calculate the average sales price for the same period last year, you can use the SAMEPERIODLASTYEAR function:

avg==AVERAGE(SAMEPERIODLASTYEAR('Sales'[Date]))

You can then create a pivot table with the month and year as rows, and the average sales price for the current year and same period last year as columns.

For the second report, you can use the AVERAGEIF function to calculate the fixed average sales price for the 12 months of the last year:

re==AVERAGEIF('Sales'[Date],">="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),"<"&DATE(YEAR(TODAY()),MONTH(TODAY()),1))

To calculate the moving average sales price for several months of the present year, you can use the AVERAGE function in combination with the OFFSET function:

res=AVERAGE(OFFSET('Sales'[Sales Price],COUNTA('Sales'[Sales Price])-6,0,6))

This formula calculates the average of the last 6 sales prices.

You can then create a pivot table with the month and year as rows, and the fixed average sales price for the last year and moving average sales price for several months of the present year as columns.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RuslanF]

Hi Rongtie, 

Thank you very much I will try your recommendations and accept it as a solution if it works. It's strange that you can't access the file I shared. Let me try another drive then:

https://docs.google.com/spreadsheets/d/1XtWrELVRI3tgFcHN3SxXZwxublWK0qS_/edit?usp=sharing&ouid=10570...

Please let me know if you are able to access the file. I am sure it helps you to better understand what kind of report I need to get. 

I also try to attach some pictures here. 

The data to calculate average sales prices is located on the tab SalesOrders

On the tab Pivot Tables the top three rows above the pivot tables is what I need to get for each month of the present year compared to the last one:

The next 5 pictures explain the formulas of the manual calculation:

So I manually change the date filter month by month to get the top 3 rows changed.

And finally need to get the following. 

This is for the scenario with the fixed 12-mo avg sales prices of the last year and cumulative monthly avg price of this year. And the same I do to get the report for cumulative average prices that changing for the same period of the last and this year:

Thanks again.