Dax Help! Average Volume Previous Quarter and Previous Month - Relative, and Partial Periods!

[ssrinath]

Hello, 

Posting for a follow-up on the Quarter-over-Quarter calcualtions!

Semantic model structure - 

• I have a date dimension table which has data from 2021-2025, full years. 
• I have a fact table from where I calculate the Volume, which follows a similar logic - Count('Fact table'[Order ID]).
• I have a One-to-Many relationship between 'Calendar Date'[Calendar_Date]-'Fact Table'[Order_Date].
• I have a measure to identify the true end of the period - end of Quarter, and end of Month.
• I have measures that identifies all the Calendar Days in the current period, last quarter, and last month using EDATE( date, -1 or -3) and  a COUNTROWS on the Calendar Table to get the number of days in the period (month or quarter).

My Calculation requirements -

• I need to calcualte the relative period change for Volume and Average Volume. As its relative, I need to handle the changes for partial periods, so built-in functions like PREVIOUSMONTH or PREVIOUSQUARTER won't work for me. I need to iterate over the Day level, then roll up to Day-Month, and finally Day-Month-Quarter depending on if I am looking at the Previous Quarter, or the Previous month as the value will return the releative reference accordingly. 
• For true end of periods, to accomodate for the different lengths in quarters and months, the Volume of the missing days must be aggregated. This way, even the days that can not be directly referernced such as 6/31 will ensure that the full previous sequential range (5/1-5/31 for previous month, 1/1-3/31 for previous quarter) will be included. 
• I was able to do this with Volume, where its being calculated over from a Quarter. Quarter-Month, and Quarter-Month-Day, Month, and Month-Day level in my Matrix. 
• My problem is with the Average Volume. The Average Volume is calculated as DIVIDE([Vol of previous period]/[calendar days of previous period]);
• No matter what I am doing, for whatever reason, I seem to be getting the Volume of the Previous Month or the Volume of the Previous Quarter. 

Here's some sample data attached to show you the drill down that I need to configure. 

At the day level, the Calendar Days Interval for Current, PQ, and PM is always 1. The spill-over logic is that it should include the Average Volume for the missing days as with June 30, where the Volume PQ is 5 which is the Average Volume for March 30 + the Average Volume for March 31, but the denominator is still 1. For all other days, like the case with July 1 and July 2, it should refererence the appropriate Previous Day intervals by Quarter, or Month to get the appropriate Average Volume.

Any help at all will be greatly appreciated! 

[v-nmadadi-msft]

Hi  @ssrinath ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. Please feel free to contact us if you have any further questions.

Thanks and regards

[v-nmadadi-msft]

Hi @ssrinath 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[v-nmadadi-msft]

Hi @ssrinath ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[v-nmadadi-msft · Accepted solution]

Hi @ssrinath  ,
Thanks for reaching out to the Microsoft fabric community forum.


Your current setup for calculating Volume over dynamic periods like previous month or quarter is solid. However, the challenge with Average Volume stems from how the date context is being interpreted at the day level  especially during end of period spillovers (e.g., comparing June 30 to March 30–31).

When calculating metrics like Average Volume, relying solely on built-in DAX functions such as PREVIOUSMONTH() or PREVIOUSQUARTER() may not suffice, especially when dealing with partial periods or custom fiscal calendars. Instead, you can use a combination of DAX functions to define the exact start and end dates of the previous period.

 CurrentDate: Captures the latest date in the current filter context.

 StartOfPrevMonth: Calculates the first day of the previous month by moving back two months to get to the end of the month before the previous and then adding one day.

EndOfPrevMonth: Determines the last day of the previous month by moving back one month from the current date.

PrevMonthDates: Generates a table of dates between StartOfPrevMonth and EndOfPrevMonth, ensuring that all dates from the 'Calendar' table are considered by removing any existing filters.

EOMONTH function (DAX) - DAX | Microsoft Learn

This approach is particularly useful when you need precise control over date ranges, such as calculating metrics for custom periods.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[ssrinath]

Thanks so much for the detailed response. 

I'll definetly check this out and will let you know!

[FBergamaschi]

Hi,

your explanation looks ok but the picture does not help. Can you please attach one pictures in which you point the issue in a clearer way? Maybe drawing some lines so we can focus the problem. Also, the sample data should be tables to import in power bi to replicate the problem, a pbix would be even better so we have also the model in place and we can check the DAX and fix it

[ssrinath]

Absolutely. 

Its very hard to replicate this into Power BI itself as the ideal functionality is not something I am able to configure. 

But here are better pictures for you based on the sample data. This is at the lowest level, which is Day. This is modeled after a Matrix visual in Power BI. So the idea is, not all the months are expanded down to day - only some are to show you the ideal functionality and how at the Day level, the Average Volume PQ and PM must reference the Day in the Prior Sequential period accordingly at a Quarter, Quarter-Month, and Quarter-Month-Day level, and Month, and Month-Day level based on the calculation.

Sample Data -1 - DaySample Data 2 - DaySample Data - 3 - Day

Now, at a Quarter-Month level, this is what it should do. For completed periods, it needs to compare the full periods at Quarter-Month level. But, for partial periods such as July where the range is only 7/1-7/2, it should compare against the relative period based on the previous sequential Quarter and Month. Sample Data - Month

Finally, at the Quarter level, it needs to compare full completed quarters against each other. But, when it compes to relative quarters such as Q3 where the range is only 7/1-7/2 it should compare against the against the relative period based on the previous sequential Quarter.

Sample Data - Quarter

Happy to clarify further if needed