Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Previous Period Difference and Percent Change

 

 

Need Help
Tutorial Requests
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi, Anonymous 

    You can try to create  calculated columns as follows

     

    Pre_Sche Chh Booked Hrs = 
VAR Pre_date =
    CALCULATE (
        MAX ( 'dim Date'[Date] ),
        'dim Date',
        'dim Date'[Date] < EARLIER ( 'dim Date'[Date] )
    )
RETURN
    CALCULATE (
        SUM ( 'dim Date'[Sche Chh Booked Hrs] ),
        'dim Date',
        'dim Date'[Date] = Pre_date
    )
    diff = 
IF (
    ISBLANK ( 'dim Date'[Pre_Sche Chh Booked Hrs] ),
    BLANK (),
    'dim Date'[Sche Chh Booked Hrs] - 'dim Date'[Pre_Sche Chh Booked Hrs]
)
    Growth % = 
IF (
    ISBLANK ( 'dim Date'[diff] ),
    BLANK (),
    'dim Date'[diff] / 'dim Date'[Pre_Sche Chh Booked Hrs]
)

     

    The result will show as below:


    Please check my sample file for more details.

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous  This can be done if you add another column to your table for YearPeriod - it should be unique so will look something like:

    2020-03-1 for March period 1

    2020-04-2 for April period 2

    Then you can create a DAX Measure and use a variable to get the previous period.

     

    It will be similar to the below: 

    PrevPeriod Hours =

    VAR _MaxPeriod = MAX(DimDate[YearPeriod])

    VAR _prevPeriod = MAXX(FILTER(DimDate, DimDate[YearPeriod] <_MaxPeriod), DimDate[YearPeriod])

    RETURN

    CALCULATE([hours], DimDate[YearPeriod] = _prevPeriod)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      We ended up created our previous measures and incorporated the previous period in our data model, which helped immensely.

       

      Actual Chg Hrs Last Period =
      var LastPeriod = max('dim FiscalFinancialPeriod'[PreviousPeriod])
      var ReturnThis =
      CALCULATE(SUM('fact ActualHours'[hours])
      , FILTER(ALL('fact ActualHours'), 'fact ActualHours'[ChargeType]="Chargeable")
      , FILTER(ALL('dim FiscalFinancialPeriod'), 'dim FiscalFinancialPeriod'[FiscalFinancialPeriod]=LastPeriod)
      )
      return (ReturnThis)
       
      Actual Chg Hrs Last Week =
      var CurrentWeek = LASTDATE('dim Date'[FirstOfWeek])
      var LastWeek = DATEADD(CurrentWeek, -7, DAY)

      var ReturnThis =

      CALCULATE(SUM('fact ActualHours'[hours])
      , FILTER(ALL('fact ActualHours'),'fact ActualHours'[ChargeType] = "Chargeable")
      , FILTER(ALL('dim Date'), 'dim Date'[FirstOfWeek]=LastWeek) )

      return (ReturnThis)
       
      We then calculated the percent change from the previous/current period measure we had. This resulted in the percent change that we were looking for.
       
      Thanks for your solution! It helped!
  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

    You can try to create  calculated columns as follows

     

    Pre_Sche Chh Booked Hrs = 
VAR Pre_date =
    CALCULATE (
        MAX ( 'dim Date'[Date] ),
        'dim Date',
        'dim Date'[Date] < EARLIER ( 'dim Date'[Date] )
    )
RETURN
    CALCULATE (
        SUM ( 'dim Date'[Sche Chh Booked Hrs] ),
        'dim Date',
        'dim Date'[Date] = Pre_date
    )
    diff = 
IF (
    ISBLANK ( 'dim Date'[Pre_Sche Chh Booked Hrs] ),
    BLANK (),
    'dim Date'[Sche Chh Booked Hrs] - 'dim Date'[Pre_Sche Chh Booked Hrs]
)
    Growth % = 
IF (
    ISBLANK ( 'dim Date'[diff] ),
    BLANK (),
    'dim Date'[diff] / 'dim Date'[Pre_Sche Chh Booked Hrs]
)

     

    The result will show as below:


    Please check my sample file for more details.

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.