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
MFelix's avatar
MFelix
Icon for Super User rankSuper User
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    Hi Anonymous

     

    Try the following code

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        6 years ago

        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.