Forum Discussion

cristianml's avatar
cristianml
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

Calculate % increase from last Period (Month)

Hi,   I would like to calculate the % increase of a measure from one month to another : Actual LCR = CALCULATE(AVERAGE('Resource Actual'[Quantity]),'Resource Actual'[Category]="Cost Rate")   I ...
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • reuben521's avatar
    reuben521
    6 years ago

    cristianml 

     

    Wow, no kidding that your data model is complex. However, having a dim_date table is cruicial for any data model, because the dim_date table should be the main source to slice and calculate around your fact table, in this case, it is your period table.

     

    Also, creating a dim_date table won't add complexity to your data model at all, because it will always be a one(dim_date) to many (other fact tables) relationship, and there is no ambiguity.

    After you have the dim_date table, just write:

     

    calculate(actual, previsoumonth(dim_date))

     

    that should work.

     

    Hope this helps

  • Anonymous's avatar
    Anonymous
    6 years ago

    Those're actually two separate measures.

    Measure 1: Last Months Actual LCR

    Actual LCR LM =
VAR last_date = LASTDATE(ALL('Resource Actual'[Date Column]))
VAR current_date = LASTNONBLANK(DateDimension[Date],[Actual LCR])
VAR ly_date = NEXTDAY(PREVIOUSMONTH(current_date))
VAR date_context =
    DATESBETWEEN(
        DateDimension[Date]
        ,NEXTDAY(PREVIOUSMONTH(current_date))
        ,LASTDATE(current_date)
    )
VAR result = CALCULATE([Actual LCR],DATEADD(date_context,-1,MONTH))
RETURN
result

    Measure 2: Variance

    Variance = DIVIDE(
   [Actual LCR] - [Actual LCR LM]
   ,[Actual LCR LM]
) * SIGN([Actual LCR LM])
Anonymous's avatar
Anonymous
Not applicable
6 years ago

You've got the variance caluclation correct, you just need to actually calculate the previous month.

Actual LCR = CALCULATE(
   AVERAGE('Resource Actual'[Quantity])
   ,'Resource Actual'[Category] = "Cost Rate"
)

Actual LCR LM =
VAR last_date = LASTDATE(ALL('Resource Actual'[Date Column]))
VAR current_date = LASTNONBLANK(DateDimension[Date],[Actual LCR])
VAR ly_date = NEXTDAY(PREVIOUSMONTH(current_date))
VAR date_context =
    DATESBETWEEN(
        DateDimension[Date]
        ,NEXTDAY(PREVIOUSMONTH(current_date))
        ,LASTDATE(current_date)
    )
VAR result = CALCULATE([Actual LCR],DATEADD(date_context,-1,MONTH))
RETURN
result

Variance = DIVIDE(
   [Actual LCR] - [Actual LCR LM]
   ,[Actual LCR LM]
) * SIGN([Actual LCR LM])

This should get you what you want if not let me know.

  • cristianml's avatar
    cristianml
    Icon for Post Prodigy rankPost Prodigy
    6 years ago

    Hi Anonymous ,

     

    Could you please help me to fix the error ?:

     

    Thanks.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Those're actually two separate measures.

      Measure 1: Last Months Actual LCR

      Actual LCR LM =
VAR last_date = LASTDATE(ALL('Resource Actual'[Date Column]))
VAR current_date = LASTNONBLANK(DateDimension[Date],[Actual LCR])
VAR ly_date = NEXTDAY(PREVIOUSMONTH(current_date))
VAR date_context =
    DATESBETWEEN(
        DateDimension[Date]
        ,NEXTDAY(PREVIOUSMONTH(current_date))
        ,LASTDATE(current_date)
    )
VAR result = CALCULATE([Actual LCR],DATEADD(date_context,-1,MONTH))
RETURN
result

      Measure 2: Variance

      Variance = DIVIDE(
   [Actual LCR] - [Actual LCR LM]
   ,[Actual LCR LM]
) * SIGN([Actual LCR LM])