cancel
Showing results 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.

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 1 Month 2 Variance: Month 2 - Month 1 % Variance: Variance / Month 1 10.00 12.00 2 20.00%

I think I can use EDATE(TODAY(),-1)) or something like that in the measure but not sure how.

Thanks.

2 ACCEPTED SOLUTIONS
Frequent Visitor

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

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])```
9 REPLIES 9

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.

Post Prodigy

Hi @Drewdel ,

Could you please help me to fix the error ?:

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

Try this:

```var refMonth = PREVIOUSMONTH(Dim_date[Date])

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

Hi @reuben521 ,

Is not working:

Frequent Visitor

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!

Frequent Visitor

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.

Post Prodigy

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.

Thanks,

Frequent Visitor

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

Announcements

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

Power BI Monthly Update - April 2024

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

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors