Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
cristianml
Post Prodigy
Post Prodigy

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 think that can be done by DIVIDE the "Actual LCR" Measure by the same but from last month.
 
Example:
 
Month 1Month 2Variance: Month 2 - Month 1% Variance: Variance / Month 1
            10.00            12.00220.00%
 
I think I can use EDATE(TODAY(),-1)) or something like that in the measure but not sure how.
 
LCR.jpg
 
Thanks.
 
 
2 ACCEPTED SOLUTIONS

@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

View solution in original post

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

View solution in original post

9 REPLIES 9
Drewdel
Advocate II
Advocate II

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.

Hi @Drewdel ,

 

Could you please help me to fix the error ?:

 

ERROR.png

Thanks.

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])
reuben521
Frequent Visitor

 Try this:

var refMonth = PREVIOUSMONTH(Dim_date[Date])

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

Hi @reuben521 ,

 

Is not working:

 

increase.jpg

 

 

@cristianml 

 

Let me know if the measure works after creating the dim_date table. if it does, can you please flag my reply as solution.

 

Thanks!

in order to have the time intelligence function to work you need to create a dim_date table, and join the date to your date in the List period table.

 

I use this code to create dim_date table. you can click on new table and copy the code in:

 

Dim_date = 
var maxYear = YEAR(TODAY())+2
return
ADDCOLUMNS(FILTER(CALENDARAUTO(), YEAR([Date]) <= maxYear),
                                            "Calendar Year", YEAR([Date]),
                                            "Calendar Year Label", "CY "&YEAR([Date]),
                                            "Year-Month", FORMAT([Date], "yyyy-mm"),
                                            "Month-Day", FORMAT([Date], "mm-dd"),
                                            "Month Name", FORMAT([Date], "mmmm"),
                                            "Month Number", MONTH([Date]),
                                            "Weekday", FORMAT([Date],"dddd"),
                                            "Weeday Number", WEEKDAY([Date]),
                                            "Day", DAY([Date]),
                                            "Quarter" , "Q"&TRUNC((MONTH([Date]) - 1)/3 + 1)
                                            )

after you create the dim_table, change the list period[date] to dim_date[date], and it should work.

Hi @reuben521 ,

 

I can't create another table as the model has many relationships with the actual Period table. I would like to find another way. I only need to create a Measure that could calculate the following :

 

Actual LCR / LCR from previous month

 

As I already have the measure "Actual LCR" I need create another measure with this but adding or filtering by previous month so I can divide both.

PBI STRUCTURE.png

 

Thanks,

 

@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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.