Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Transform Quarter Acumlated Values in Month Values

Hi Guys, i have some quarter acumlated values in my table, and i need to see these values in month view!

 

This is the acumulated values that i have:

 

 

 

 

 

 

 

 

 

 

And this is the result that i need... just one column with the non acumulated values

 

 

 

 

 

 

 

 

Doesn't matter if it´ll be with DAX or M... just need this!

 

Someone help me???

 

Thanks

  • parry2k's avatar
    parry2k
    8 years ago

    Hey, I tried it and I hope it will work for you:

     

    - Add new column for ranking the data

     

    Rank = RANKX(Filter(Table1, Table1[Category] = EARLIER(Table1[Category])), Table1[Date],,ASC,Dense)

    - Add measure to get the value

     

    Cummulative Total Unwind = 
var getPreviousRowRank = FIRSTNONBLANK(Table1[Rank],1) - 1
var getCurrentCategory = MAX(Table1[Category])
var getCurrentRowValue = MAX(Table1[Value])
var getPreviousRowValue = CALCULATE(MAX(Table1[Value]), Filter(ALLSELECTED(Table1), Table1[Category] = getCurrentCategory && Table1[Rank] = getPreviousRowRank))
return if(getPreviousRowValue = BLANK(), getCurrentRowValue, getCurrentRowValue - getPreviousRowValue)

     

    Drop a table visual and add following to value:

     

    - Date

    - Category

    - Cummulative Total Unwind (new measure)

     

     

    And I guess you will get the data you are looking for

     

    PS - Replace table name with your table name in DAX and also replace category with Cod and Value with Valor

     

     

1 Reply

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    Hey, I tried it and I hope it will work for you:

     

    - Add new column for ranking the data

     

    Rank = RANKX(Filter(Table1, Table1[Category] = EARLIER(Table1[Category])), Table1[Date],,ASC,Dense)

    - Add measure to get the value

     

    Cummulative Total Unwind = 
var getPreviousRowRank = FIRSTNONBLANK(Table1[Rank],1) - 1
var getCurrentCategory = MAX(Table1[Category])
var getCurrentRowValue = MAX(Table1[Value])
var getPreviousRowValue = CALCULATE(MAX(Table1[Value]), Filter(ALLSELECTED(Table1), Table1[Category] = getCurrentCategory && Table1[Rank] = getPreviousRowRank))
return if(getPreviousRowValue = BLANK(), getCurrentRowValue, getCurrentRowValue - getPreviousRowValue)

     

    Drop a table visual and add following to value:

     

    - Date

    - Category

    - Cummulative Total Unwind (new measure)

     

     

    And I guess you will get the data you are looking for

     

    PS - Replace table name with your table name in DAX and also replace category with Cod and Value with Valor