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!Curve color depending on its position relative to another
I have 2 series of values: val1 and val2, and a column with dates. I want to show a graph with a date x-axis, and the 2 curves corresponding to val1 and val2. The 2nd curve should be red if below curve1 and green if above. The first solution I came with is to create 2 columns corresponding to val2 in my database: val2_up would have the same value as val2 if above val1, and would be NULL if below val1. Similarly, val2_down would have the same value as val2 if below val1, and would be NULL if above val1. The problem if I choose this solution is that I cannot add filters on family, category and type. Actually I could, but as PowerBI computes the total, If I choose to show for example only family1, it would compute 2 totals: the first with every values below val1 and the 2nd with every value above val1. Thus it would show 2 curves for val2, whereas I would only want 1: If you have any other idea, I would appreciate if you could share it
"Dynamic" Between Date Slicer with both Relative and Default Dates
Using a date slicer we would like for the date to dynamically display the last 30 days of data by default, however also allow for the user to change those dates to pull in more data. The only way we've found to do this is to set both the header and filter to Relative - however when doing this you lose the "Date Format" in the header slicer which becomes a selection list of Last/Next/This 30 Days/Months/Weeks. While this produces the desired data sets and allows for both dynamic and default dates in the slicer, its not end user friendly since they have to figure out how many days or months to go back vs just clicking some dates. Has anyone come up with other ideas to handle this?
