Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Problem with Sorting on Measure in a Matrix affected by Calculation Group

I created simple measure Sales $ = CALCUALTE(SUM(MyTable[Sales])), to see data for entire year I created calculation group that removes filter from my DateDimension and pulls back Last 12 Months worth of data as seen in Figure 2. However sorting by Sales $ is not giving me expected results as what I can see in Figure 1. It took me a while to realize that sort of Figure 2 was done the same what as Figure 3 which does not have Calculation Group applied to it. 

 

Is there simple way to resolve this? I considered leaving it as a table and then just hiding the calculation group column. But was wondering if there is cleaner and better way to resolve this.

 

 

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Anonymous , You can get rolling 12 month data like

     

    Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

     

    Rolling 12 = CALCULATE([Net], WINDOW(-11,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

     

     

    Or use Matrix visual and move the name of Calculation group on column