Average Volume - Quarter over Quarter, Month over Month

[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 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-kpoloju-msft]

Hi @ssrinath,

Thank you for reaching out to the Microsoft fabric community forum.  I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.

I am also including .pbix file for your better understanding, please have a look into it:
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[ssrinath]

Thanks so much for your response. 

Unfortunetly, it doesn't work at the Day level. It forces the AverageVolume_PM down all rows when expanded out to the Day-level hierarchy. 

The calendar days_PM  interval at the day hierarchy level should always be 1 as you're only iterating over a single calendar day.

At the month heirarchy level, the calendar days_PM interval should be the the interval of the previous month. So your AverageVolume_PM for July 1 should refernece the AverageVolume_PM for June 1, which it does not do as it should be 1/1, which is 1 (Volume_PM/CalendarDays_PM) .

[v-kpoloju-msft · Accepted solution]

Hi @ssrinath,

Thanks so much for the detailed follow-up question.

You're right when you're looking at the Day level, the AverageVolume_PM should divide by 1, because you're only dealing with one day’s data. But when you roll up to the Month or Quarter level, the denominator should reflect the actual number of calendar days in that previous period.

To make this work properly in the visual, we can adjust the AverageVolume_PM measure to check the level you're currently viewing. This way, it behaves differently depending on whether you are looking at Day, Month, or Quarter.

AverageVolume_PM =

VAR IsDayLevel = ISINSCOPE('Calendar'[Date])

VAR VolPM = [Volume_PM]

VAR DaysPM = [Calendar Days_PM]

RETURN

    DIVIDE(VolPM, IF(IsDayLevel, 1, DaysPM))

This makes sure: At day-level, the denominator is always 1. At month or quarter level, it uses the actual number of days from the previous month or quarter.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[v-kpoloju-msft]

Hi @ssrinath,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you!

[ssrinath]

Sorry abut this - I just saw this. 

I'll implement this and get back to you. Thank you so much for your response!

[v-kpoloju-msft]

Hi @ssrinath,

Thank you for the followup question. Take your time testing it out, and feel free to reach back out if you run into any issues or have further questions.

Looking forward to hearing how it goes.

Thank you for using the Microsoft Fabric Community Forum.

[v-kpoloju-msft]

Hi @ssrinath,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @ssrinath,

Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[v-kpoloju-msft]

Hi @ssrinath,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.