Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic time period measure

Hi, I would like to have a measure that adjusts to the time periods displayed in columns in a matrix, particularly when I am using a date with hierarchy and then the user expands to the next level. ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Apologies - correction to the above formula.  I had to be more explicit in my evaluation of the combination of the inscope variables.  The logic in my original formulat using the NOT(...)s was incorrect.

 

Total Combined Revenue Prior Period = 
VAR _Expanded_Year = ISINSCOPE(Dates[Date].[Year])
VAR _Expanded_Quarter = ISINSCOPE(Dates[Date].[Quarter])
VAR _Expanded_Month = ISINSCOPE(Dates[Date].[Month])
Return

 SWITCH(TRUE(),
 _Expanded_Year = TRUE() && _Expanded_Quarter = FALSE() && _Expanded_Month = FALSE(), [Total Combined Revenue Prior Year],
 _Expanded_Year = TRUE() && _Expanded_Quarter = TRUE() && _Expanded_Month = FALSE(),[Total Combined Revenue Prior Quarter],
 _Expanded_Year = TRUE() && _Expanded_Quarter = TRUE() && _Expanded_Month = TRUE(),[Total Combined Revenue Prior Month]
 )