How to Use a Historical Monthly Average for the Furture Months?

[ymobbs]

Hi, 

I have a project that I need to calculate the average selling price (ASP) from certain months then use it as the ASP for future months.  

I have a simplified table of my project:

Customer	Month	Volume	Revenue
A	JAN	20	100
B	JAN	25	150
A	FEB	15	125
B	FEB	30	500
C	FEB	23	400
A	MAR	21	350
B	MAR	50	750
C	MAR	24	160
D	MAR	85	1200
E	MAR	65	950
E	APR	35
B	APR	26
B	MAY	45
C	MAY	24
C	MAY	59

In this example, I need to calculate the ASP from Jan, Feb and Mar then use it as the ASP for Apr and May in a Table or Matrix in Power BI. I tried Calculate but the filter part of the meausre made it only calculate when the months are JAN or Feb or Mar. I'm totally lost here. Any help would be appreciated!!

[v-jingzhang · Accepted solution]

Hi @ymobbs 

You can try this measure

ASP = 
var _lastMonth = CALCULATE(MAX('Table'[Month Number]),ALL('Table'),'Table'[Revenue]<>BLANK())
var _totalRevenue = CALCULATE(SUM('Table'[Revenue]),ALL(Months[Month]),'Months'[MonthNumber]<=_lastMonth)
var _totalVolume = CALCULATE(SUM('Table'[Volume]),ALL(Months[Month]),'Months'[MonthNumber]<=_lastMonth)
return
IF(MAX(Months[MonthNumber])>_lastMonth,DIVIDE(_totalRevenue,_totalVolume))

I create two Dim tables Customers and Months with below code and use them in the matrix visual. This would avoid missing data when there is no data in some months in the data table. And create relationships between Dim tables and Data table. 

Customers = VALUES('Table'[Customer])

Months = DISTINCT(SELECTCOLUMNS('Table',"MonthNumber",'Table'[Month Number],"Month",'Table'[Month]))

Result

Download the attachment to see details. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[ymobbs]

@v-jingzhang Thank you so much! I was able to plug the measure in to make it worked. 

However, it might a issue once the time goes forward when it includes 2 years of data. When it comes, the measure has to change from basing on "Month" to "Date". No rush, but would you please adjust the measure to make it work when mutiple of years of data included. Thank you again!

[v-jingzhang]

Hi @ymobbs 

When you have Date column and data of multiple years, I recommend to add a Date table to the model. Build a relationship between Date table and the fact table on their Date columns (one-to-many, single filter direction). In the Date table, add a YearMonth column to have values like 202201, 202202, 202203... for months in all years. Then use this YearMonth column to replace the original MonthNumber column in my previous measure. Remove the Months table in my previous reply and use the Date table instead. 

Using a Date table is a common and recommended practice when you want to make date-related calculations.  

Do You Need a Date Dimension? - RADACAD

Power BI Date or Calendar Table Best Method: DAX or Power Query? - RADACAD

Best regards,

Jing

[ymobbs]

@v-jingzhang Thank you again for such a through explanation and your patience! 

[ymobbs]

	Apr	Apr	May	May
	Volume	ASP	Volume	ASP
C
B
E

@amitchandak Thank  you so much for your response! 

However, I don't quite understand the formulas you wrote. What is the ASP in the first three measures? I don't have it in my original data tabel.  Which one of the measures that I can put in the Values in a Matrix (Above) to show the ASP for Apr and May?

[amitchandak]

@ymobbs , You can get formula likes

MTD ASP= CALCULATE([ASP] ,DATESMTD('Date'[Date]))
last MTD ASP= CALCULATE([ASP],DATESMTD(dateadd('Date'[Date],-1,MONTH)))

2nd last MTD ASP= CALCULATE([ASP],DATESMTD(dateadd('Date'[Date],-2,MONTH)))

using today

This month Today =
var _min = eomonth(today(),-1)+1
var _max = eomonth(today(),0) //today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max ) )

last month Today =
var _min = eomonth(today(),-2)+1
var _max = eomonth(today(),-1) //today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max ) )