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.

v-jingzhang's avatar
v-jingzhang
Community Support
5 years ago

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'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?