Forum Discussion
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 t...
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 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.
Awesome finally! Thanks a lot! Worked just fine, I just had to change SELECTEDVALUE to MAX since it wasn't available in Power Pivot 🙂