Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
v-jingzhang
Community Support
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

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