I've attached a test model with what it is I am trying to achieve. There are different comparative views that the EU can utilize to see different reporting periods. My challenge is this
I have a calculated dimension that originally i suppressed nulls to ensure that rangesum in the expression did not still autopopulate all the other dates
If I suppress nulls, then the underlying values get removed from the rangesum. This is NOT what I want to happen
If I use a straight MonthName type field then all dates get populated. If a user is on 12 month rolling, then they should only ever see 12 MonthNames in the pivot table. If the user is on MTD, then only 1 month
The expression I have is a pick match that handles how to aggregate based on this switch of periods
I cannot get this to pivot correctly with the right periods and the right values with suppress nulls enabled. How do i get this right all
I've added a false value to the calculated dimension that reads 'TOTAL'. This way I represent the null as a subtotal value when populating the calendar periods. This makes things look cleaner but would still like to know if there is a way around this
