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

Hi,

 

I ultimately solved this by inspecting INSCOPE for each of the Year, Quarter, Month dimensions behind the Dates[Date] field.  this was necessary as .Year always returns true regardless of the expanded level, .Quarter always returns true regardless of expanded level unless only .Year is expanded, etc.  My final solution is below.

 

I saw some comments that best practice is to not use the date hierachy and instead use separate Year, Quarter, Month, Day columns.  However I could not find clear arguments for either method on the community or other web resources.  Can anyone point me to discussion threads on the benefits of each approach?

 

Thanks

 

Total 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 && NOT(_Expanded_Quarter) && NOT(_Expanded_Month), [Total Revenue Prior Year],
 _Expanded_Year && _Expanded_Quarter && NOT(_Expanded_Month),[Total Revenue Prior Quarter],
 _Expanded_Year && _Expanded_Quarter && _Expanded_Month,[Total Revenue Prior Month]
 )
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]
 )