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 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!

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.

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

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

       

       

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