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.  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,

 

Need Help

9 Replies

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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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.