Dynamic time period measure

[Anonymous]

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.  As an example, I would like to calculate Sales Last Period (or equally Change in Sales or Percentage Growth) to display along side Sales Current Period.  When the date column granularity is at the Year level, the "last period" should be the prior year.  When granularity is expanded to the Quarter level, "last period" should be the prior quarter, etc., etc..

I know I can hard code measures with the Calculate and Dateadd function using the -1, YEAR parameter (and QUARTER, and MONTH respectively), but this hard coding means that I need to have at least 3 different measures for year, quarter, and month - and then 3 different visuals each using the respective measure.  However, ideally I would like for a single measure to handle this more dynamically and adjust to the context / granularity being displayed.  I have worked through all the time intelligence functions and I can't see how to accomplish this dynamic time period measure.

Any suggestions on approach on the above?

Thanks,

[amitchandak]

@Anonymous , I created 1 such measure few days back. Now this was for Current Employee vs Last period employee

Last Period Employee =
var _min_date = minx(all('Date'),'Date'[Date])
var _Expression=if(ISFILTERED('Date'[Month Year]),maxx('Date',ENDOFMONTH(DATEADD('Date'[Date],-1,MONTH))),maxx('Date',DATEADD('Date'[Date],-1,YEAR)))
Return
CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=_Expression && Employee[Start Date]>=_min_date && (ISBLANK(Employee[End Date]) || Employee[End Date]>_Expression)),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

You can check more details at: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

I think is in scope and isfiltered can help to so.

[MFelix]

Hi @Anonymous ,

Has you refer the calculation needs to be made in 3 different calculations one for each of the periods you need, however this can be wrap around a switch function to catch the hierarchy level at wich you are and then return the correct value.

[Anonymous]

OK - many thanks for the guidance.  I had not thought of using the nested if logic with SWITCH.  What is the best function to evaluate the hierachy level [i.e., the function to put inside the SWITCH(TRUE(), function???(Dates.[Date]), CALCULATE(.... ]?

Thanks,

[MFelix]

Hi @Anonymous

Try the following code

Measure =
SWITCH (
    TRUE ();
    ISINSCOPE ( Dates[year] ); [Measureyear];
    ISINSCOPE ( Dates[quarter] ); [Measurequarter];
    ISINSCOPE ( Dates[month] ); [Measure month]
)

[Anonymous]

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.

[Anonymous]

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]

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]
 )

[MFelix · Accepted solution]

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