Forum Discussion

juandroid's avatar
juandroid
Helper I
8 years ago
Solved

Creating a group Column

Hi Guys!!   I have the next data:   account   value      date 1105         300       01/17 1110         100       01/17 1120         300       01/17 1105         500       02/17 1110        ...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi juandroid,

    I try to reproduce your scenario and get expected result, please follow the steps below.

    1. Create a calculated column to get month.

    month = MONTH('table'[date])


    2. Create a calculated column to get the previous month's value.

    previous-month value = LOOKUPVALUE('table'[value],'table'[account],'table'[account],'table'[month],'table'[month]-1)



    3. Create a measure using the formula. Finally create a table visual to display it.

    result = IF(FIRSTNONBLANK('table'[month],1)=1,BLANK(),IF((SUM('table'[value])-SUM('table'[previous-month value]))>0,"source","use"))

    result table

     

    Best Regards,
    Angelia

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

You do not need to write a measure.  Just create this calculated column formula

 

=if(ISBLANK(CALCULATE(LASTNONBLANK(Table1[Value],MAX([Date])),FILTER(Table1,Table1[Account]=EARLIER(Table1[Account])&&[Date]<EARLIER([Date])))),BLANK(),if([Value]-CALCULATE(LASTNONBLANK(Table1[Value],MAX([Date])),FILTER(Table1,Table1[Account]=EARLIER(Table1[Account])&&[Date]<EARLIER([Date])))>0,"Source","Use"))

Hope this helps.