Measure to start from a specific date

[Anonymous]

Hi Expert

I am trying re create the following Sumproduct formula as shown in the table image below in Power BI. I want the measure to Shart at Feb-20 and always get the Sumproduct of the previous 6 periods.

See atatched smaple PBIX

https://www.dropbox.com/s/ii28rhnl5gqawej/Test.pbix?dl=0 

[d_gosbell]

You cannot do what you want with your sample data because you only have a string version of the month name. So all the Aprils sort first then the Augusts, etc.

So I created the following calculated column so that the months can be sorted correctly

MonthKey = DATEVALUE("1-" & [Month])

Then you can create a measure like the following

Measure =

var _currPeriod = max('Sample'[MonthKey])

var _last6 = topn(6, Filter(ALL('Sample'[MonthKey]), 'Sample'[MonthKey] < _currPeriod), 'Sample'[MonthKey])

var _last6Cnt = COUNTROWS(_last6)

var _numerator = CALCULATE(SUMX( 'Sample', 'Sample'[Amount] * 'Sample'[Rate]), _last6, all('Sample'))

var _denominator = CALCULATE(sum('Sample'[Amount]), _last6, all('Sample'))

return if(_last6Cnt >= 6, DIVIDE( _numerator , _denominator))

[Anonymous]

Hi - d_gosbell - Firstly thanks excellent feedback. Just one slight issues the - the final result is off by one i.e i need to move the end result up by one.....i.e. shift up one row..... 

[d_gosbell]

---

@Anonymous wrote:

Hi - d_gosbell - Firstly thanks excellent feedback. Just one slight issues the - the final result is off by one i.e i need to move the end result up by one.....i.e. shift up one row..... 

---

So I matched your expected output exactly Mar-20 is 1.26 in both and every month after that is the same also:

Are you saying you want something different to the screen shot you provided for the expected output? Does that mean that the formula at row 27 should be summing rows 20 to 25? Or do you want to include the current period, so row 27 should actually be summing rows 22 to 27?

[Anonymous]

Hi d_gosbell.....Lets assume both the options you have given
Does that mean that the formula at row 27 should be summing rows 20 to 25? Or do you want to include the current period, so row 27 should actually be summing rows 22 to 27?

For me to learn how would i amend the measure in both cases...

[d_gosbell · Accepted solution]

So going back to 20-25 is harder because your months are not continuous so you need to calculate the previous month then use that when filtering the months for the topn 

Measure Back 1 =
var _currPeriod = max('Sample'[MonthKey])
var _prevPeriod = CALCULATE(max('Sample'[MonthKey]), 'Sample'[MonthKey] < _currPeriod)
var _last6 = topn(6, Filter(ALL('Sample'[MonthKey]), 'Sample'[MonthKey] < _prevPeriod), 'Sample'[MonthKey])
var _last6Cnt = COUNTROWS(_last6)
var _numerator = CALCULATE(SUMX( 'Sample', 'Sample'[Amount] * 'Sample'[Rate]), _last6, all('Sample'))
var _denominator = CALCULATE(sum('Sample'[Amount]), _last6, all('Sample'))
return if(_last6Cnt >= 6, DIVIDE( _numerator , _denominator))

Including the current month is easier as you just change the filter condition from < to <= 

Measure Forward 1 =
var _currPeriod = max('Sample'[MonthKey])
var _last6 = topn(6, Filter(ALL('Sample'[MonthKey]), 'Sample'[MonthKey] <= _currPeriod), 'Sample'[MonthKey])
var _last6Cnt = COUNTROWS(_last6)
var _numerator = CALCULATE(SUMX( 'Sample', 'Sample'[Amount] * 'Sample'[Rate]), _last6, all('Sample'))
var _denominator = CALCULATE(sum('Sample'[Amount]), _last6, all('Sample'))
return if(_last6Cnt >= 6, DIVIDE( _numerator , _denominator))

[Anonymous]

Apologies for asking could you shed some light on - STUCK
https://community.powerbi.com/t5/Desktop/TRicky-Rolling-3-and-6-months/m-p/1942745#M739350

[Anonymous]

Excellent sir thank you.. God Bless amazing feedback - Option 2 worked