cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Calculate SUM between YearMonth (Values)

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!

1 ACCEPTED SOLUTION
Community Support

@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.

5 REPLIES 5
Community Support

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
Not applicable

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?

Community Support

@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
Not applicable

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

Super User

@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])) )

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.