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.

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