Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!!
Solved! Go to 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.
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.
@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!
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
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?
@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 ) )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
87 | |
80 | |
69 | |
69 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |