Calculate SUM between YearMonth (Values)

[Anonymous]

I have tried to create a measurement that sums the last 12 months. Most post describes how to do between date, but I want to do it by YearMonth which is formatted as a value.

How would I create the measure to get the result shown below? (Only Example made manually in excel)

I have already put a lot of time trying to solve this so I'm really greatful for help!

[v-jingzhang]

Hi @Anonymous 

If you could add an Index column into the table, it would be easy.

Measure = 
VAR curIndex = SELECTEDVALUE('Table'[Index])
RETURN
CALCULATE(SUM('Table'[Value]),ALL('Table'),'Table'[Index]<=curIndex,'Table'[Index]>curIndex-12)

If you don't want to add an Index column but want to use the original YearMonth column, we need to split the year and month part, substract 1 from year to get the previous YearMonth value and filter the YearMonth column then. To filter YearMonth values by using comparison operators in the measure, converting YearMonth column to Number data type in advance would be better. 

Measure 2 = 
VAR curYearMonth = SELECTEDVALUE('Table'[YearMonth])
VAR curYear = INT(LEFT(curYearMonth,4))
VAR curMonth = RIGHT(curYearMonth,2)
VAR previousYearMonth = INT((curYear-1)&curMonth)
RETURN
CALCULATE(SUM('Table'[Value]),ALL('Table'),'Table'[YearMonth]>previousYearMonth,'Table'[YearMonth]<=curYearMonth)

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Anonymous]

Thanks for fast replies! I realized I have another column I want to use as external filter since I have several products with a value for each YearMonth. I liked your solution without using a additional table or index column. How would I do this when the table look like the one below?

[v-jingzhang · Accepted solution]

@Anonymous Sorry for late reply. Do you mean calculate the sum of last 12 months for every product in every month? If so, try this

Measure 2 = 
VAR curYearMonth = SELECTEDVALUE('Table'[YearMonth])
VAR curYear = INT(LEFT(curYearMonth,4))
VAR curMonth = RIGHT(curYearMonth,2)
VAR previousYearMonth = INT((curYear-1)&curMonth)
VAR product = SELECTEDVALUE('Table'[ProductName])
RETURN
CALCULATE(SUM('Table'[Value]),ALL('Table'),'Table'[YearMonth]>previousYearMonth,'Table'[YearMonth]<=curYearMonth,'Table'[ProductName]=product)

Let me know whether it works or not.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Anonymous]

Awesome finally! Thanks a lot! Worked just fine, I just had to change SELECTEDVALUE to MAX since it wasn't available in Power Pivot 🙂

[amitchandak]

@Anonymous , Create a month year RANK, prefebally in new table, say Date

Month Rank = RANKX(all('Date'),'Date'[Month year],,ASC,Dense) //YYYYMM format

a new measure

rolling 12 = CALCULATE(sum('Table'[value]),filter(ALL('Date'),'Date'[Month Rank]>=max('Date'[Year])-12 && 'Date'[Year]<=max('Date'[Month Rank])) )