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!
need help this calculated table by using dax
Could you please help me create a calculated table in Power BI using DAX? I have two tables: Calendar and Inbound_Query. The Inbound_Query table includes the following columns: Market, Supply Category, Container Number, Max ETA Date, Clearance Days, and Capacity. I want to calculate a Predicted Delivery Date based on the following logic: For each day and supply category, count the number of containers. If the container count exceeds the capacity, the predicted delivery date should be: Max ETA Date + Clearance Days + 1 day. Otherwise, it should be: Max ETA Date + Clearance Days. I need a Remaining Containers column that tracks the number of containers exceeding the capacity for each day and market. These excess containers should be carried over to the next day. If the container count is within capacity, the remaining containers should be 0. The final calculated table should include the following columns: Market Supply Category Max ETA Container Count Remaining Containers Capacity Clearance Days Predicted Delivery Date
