Calculation to show MTD value

[Anonymous]

Hello, 

I have the following measure:

[Measure 100] - [Value in GBP on last day of Month (Last Month)]

where Measure 100 = 

CALCULATE([Total EOD Balance in GBP], FILTER(ALL('V3 Measure for Changes'),'V3 Measure for Changes'[rank]=max('V3 Measure for Changes'[rank])))

What this does is calculate the difference between the latest value and the value at the end of the previous month. It is always fixed as it works with a rank table which is different from the date table present. 

I would like, to however, create a calculation that can be used with a date slicer so that no matter what date is chosen it will calculated:

= [Value at that date] - [Value at the end of the previous month]

Thank you. 

Soo

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to your description, you want to calculate the value of the current date minus the value of the current date at the end of the month of the previous month. Here are my steps you can follow as a solution.

(1)This is my test data.

(2)We can create  a measure.

Measure = var _slice= SELECTEDVALUE('V3 Measure for Changes'[Date])

var _cuurent_gbp =SUMX( FILTER( ALL('V3 Measure for Changes'), 'V3 Measure for Changes'[Date] =_slice) , [EOD Balance in GBP])

var _last_month = EOMONTH( _slice , -1)

var _last_gbp =SUMX( FILTER( ALL('V3 Measure for Changes'), 'V3 Measure for Changes'[Date] =_last_month) , [EOD Balance in GBP])

return

_cuurent_gbp-_last_gbp

(3)Then the result is as follows.

If this method does not meet your needs, you can provide us with detailed input and output examples in tabular form so that we can better solve the problem for you.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@Anonymous , Last day of last month

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

or last non blank

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

https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79