Dax Help! Average Volume Previous Quarter and Previous Month - Relative, and Partial Periods!
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!
