Forum Discussion
DAX Help: Average RRP Over Last 3 Months Excluding Latest, Dividing by Available Months Only"
I’m working on a Power BI report where I need to calculate the average RRP for the last 3 months from selected date , but I want to exclude the selecetd month in the data. Here's what I mean with an example:
Let’s say the latest available month in the dataset is March 2025. I want to calculate the average RRP for the three months before that, i.e., December 2024, January 2025, and February 2025.
Now, in some cases, there might not be data for all 3 of those months. So:
If data exists for all 3 months, divide the total RRP by 3.
If only 2 months have data, divide by 2.
If just 1 month has data, divide by 1.
My current formula looks like this:
3M AVG RRP = DIVIDE(CALCULATE([RRP],DATESINPERIOD(Dim_Date[Date],EOMONTH(MAX(dim_date(date)), -1), -- Exclude the max (latest) month by using EOMONTH-3, MONTH -- Get the last 3 months (from the month before the max month))),3)How can I tweak this formula to return the average RRP over those 3 months, with the division adjusting based on how many months actually have data?"
Hi Hemant_Jaiswar,
Thank you for reaching out to us through the Microsoft Fabric Community Forum.
Kindly find attached the screenshot and the PBIX file, which may assist in resolving the issue:
If you find our response helpful, we request you to mark it as the accepted solution and consider giving kudos. This will be beneficial for other community members who may have similar queries.Thank you.
Hi,
PBI file attached.
Hope this helps.
7 Replies
Hi Hemant_Jaiswar ,
Could you please provide the sample .pbix file. So that I will try to provide exact solution.
ajaybabuinturi here is the sample data
Year Month StoreName Sales
2024 January Store A 12000
2024 January Store B 9500
2024 February Store A 13400
2024 February Store C 8900
2024 March Store B 10000
2024 March Store C 9200
2024 April Store A 11000
2024 May Store C 9700
2024 May Store B 10200
2024 June Store A 12500
2024 July Store B 10800
2024 August Store C 9500
2024 September Store A 11900
2024 October Store C 9800
2024 November Store A 12700
2024 December Store B 11500Hi,
PBI file attached.
Hope this helps.
Hi Hemant_Jaiswar,
Thank you for reaching out to us through the Microsoft Fabric Community Forum.
Kindly find attached the screenshot and the PBIX file, which may assist in resolving the issue:
If you find our response helpful, we request you to mark it as the accepted solution and consider giving kudos. This will be beneficial for other community members who may have similar queries.Thank you.
Hi Hemant_Jaiswar,
We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.
If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.
Thank you.Hi Hemant_Jaiswar,
We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.
If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.
Thank you.Hi Hemant_Jaiswar,
We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.
If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.
Thank you.
