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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Creating Measure

Hi folks,

I need help from you guys.

I have a column name- Landed rate( which is variable, every month is changing)

i need average of Landed rate for current month minus last month.

please me out with this.

Thanks

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anonymous ,

 

You can follow the below steps:

1.Add a new column in the table

   

Cur_Minus_PreviousLandRate =
VAR cur_landrate = 'Table'[LandRate]
VAR cur_month = 'Table'[Month]
VAR previous_month = cur_month - 1
VAR previous_lanrate =
    CALCULATE (
        MAX ( 'Table'[LandRate] ),
        FILTER ( 'Table', 'Table'[Month] = previous_month )
    )
RETURN
    cur_landrate - previous_lanrate

vbinbinyumsft_0-1657526645882.png


2.Create a measure to calculate the average about the new added column

 

Avg_Diff_LandRate =
AVERAGE ( 'Table'[Cur_Minus_PreviousLandRate] )

vbinbinyumsft_1-1657526891906.png

 

 

 


Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @Anonymous ,

 

You can follow the below steps:

1.Add a new column in the table

   

Cur_Minus_PreviousLandRate =
VAR cur_landrate = 'Table'[LandRate]
VAR cur_month = 'Table'[Month]
VAR previous_month = cur_month - 1
VAR previous_lanrate =
    CALCULATE (
        MAX ( 'Table'[LandRate] ),
        FILTER ( 'Table', 'Table'[Month] = previous_month )
    )
RETURN
    cur_landrate - previous_lanrate

vbinbinyumsft_0-1657526645882.png


2.Create a measure to calculate the average about the new added column

 

Avg_Diff_LandRate =
AVERAGE ( 'Table'[Cur_Minus_PreviousLandRate] )

vbinbinyumsft_1-1657526891906.png

 

 

 


Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , You can use measure that can give this month vs last month data

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

 

 

or trailing 1 month meausre

 

Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))

 

 

or

 

MTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))


LMTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = date(Year(_max1), month(_max1)-1, day(_max))
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

 

Last Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1)
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

 

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

I need average of landing rate(which is varing every month) for current and previous month. 

Not sum of sales. Please help me for this.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.