Forum Discussion
Dynamic time period measure
Hi Anonymous
Check this post by one of the great dax
master
https://www.sqlbi.com/articles/automatic-time-intelligence-in-power-bi/
Also don't forget to mark the correct answer so it can help others
Super UserHi Anonymous
Try the following code
Measure =
SWITCH (
TRUE ();
ISINSCOPE ( Dates[year] ); [Measureyear];
ISINSCOPE ( Dates[quarter] ); [Measurequarter];
ISINSCOPE ( Dates[month] ); [Measure month]
)
Hi Miguel,
Unfortunately my linked dates table does not contain separate columns for Year, Quarter, and Month. While I could add columns to break out those individually, I was hoping to solve this with solely the Date column.
Additionally, I have tried both ISINSCOPE and ISFILTERED in the formula structure below, and both only yield the correct results when the columns are collapse to the year level in the hierarchy. If I expand to the next level in the martix visuals I do not get any result for the Prior Period measure (except in the totals)
My current formula is below
Total Rev Prior Period =
SWITCH(TRUE(),
ISINSCOPE(Dates[Date].[Year]), [Total Rev Prior Year],
ISINSCOPE(Dates[Date].[Quarter]),[Total Rev Prior Quarter],
ISINSCOPE(Dates[Date].[Month]),[Total Rev Prior Month]
)
In the above, you can see I am trying to evaluate the current context in the visual for the Date hierarchy (i.e. Dates[Date].[Year]. etc).
The inscope or isfiltered approaches seem to make sense to evaluate the current context in the visual, but I am not quite getting the intended results.
Any more suggestions?
Thanks,
- MFelix
Hi amitchandak ,
Altough best practices advise that you disable the auto time and date column and have a separate column for each of the values you can redo your measure like this:
Total Rev Prior Period = VAR Minimum_Date = MIN ( Dates[Date] ) VAR Maximum_Date = MAX ( Dates[Date] ) RETURN SWITCH ( TRUE (), YEAR ( Minimum_Date ) = YEAR ( Maximum_Date ), [Total Rev Prior Year] ), QUARTER ( Minimum_Date ) = QUARTER ( Maximum_Date ), [Total Rev Prior Quarter ), MONTH ( Minimum_Date ) = MONTH ( Maximum_Date ), [Total Rev Prior Month] ) )This also does the trick.
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]
)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]
)