How to create a new column with the previous row value by group?

[scorbin-j]

I am trying to create a column that displays the previous rows data, but is grouped by both a category column and date column. Right now I have something like this: 

I would like to see results like this:

So the amount from the earlier line based on the area and date appears in the new column. Note that the date is not consecutive as the data is collected at the month end. Is there a way I can accomplish this using DAX? 

[v-zhangti · Accepted solution]

Hi, @scorbin-j 

You can try the following methods.

Previous = 
Var _Prevdate=MAXX(FILTER('Table',[Date]<EARLIER('Table'[Date])&&[Area]=EARLIER('Table'[Area])),[Date])
Return
CALCULATE(SUM('Table'[Amount]),FILTER('Table',[Date]=_Prevdate&&[Area]=EARLIER('Table'[Area])))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[scorbin-j]

That seems to be working! Thank you so much 

[scorbin-j]

So I have a partial solution. I created an index column like this: 

IndexColumn = 
   RANKX(
     ALL(Amounts),
     Amounts[Date],,
     ASC,
     Dense
   )

Then I created a this measure: 

PrevRowEmpCount = 
      CALCULATE(
          'Measure'[SUMAmounts],
           TOPN(
           1,
           FILTER(
           ALLSELECTED('Amounts'),
              Amounts[Date] < MAX(Amounts[Date])
            ),
          Amounts[Date],
          DESC
       )
    )

This works well when looking at the total Amountsover time in a table, and I can filter by the Area. However when I add the Area to a table, the measure does not work. I think it needs to be a column as well but I'm not sure how to do that. 

[grantsamborn]

OK.  I see the problem now.  I'm not sure how to implement it as a column.

Good luck.

[grantsamborn]

Hi @scorbin-j 

Try this

Previous Amount = 
VAR _Area = SELECTEDVALUE( 'Area Amounts'[Area] )
VAR _CurrDt = SELECTEDVALUE( 'Area Amounts'[Date] )
VAR _PrevDt =
    CALCULATE(
        MAX( 'Area Amounts'[Date] ),
        FILTER(
            ALL( 'Area Amounts' ),
            'Area Amounts'[Area] = _Area
                && 'Area Amounts'[Date] < _CurrDt
        )
    )
VAR _PrevAmt =
    CALCULATE(
        SUM( 'Area Amounts'[Amount] ),
        FILTER(
            ALL( 'Area Amounts' ),
            'Area Amounts'[Area] = _Area
                && 'Area Amounts'[Date] = _PrevDt
        )
    )
RETURN
    _PrevAmt

[scorbin-j]

Tried that out and right now that is just outputting blank values in the new column it created. 

[grantsamborn]

Oops.  My mistake.  I built that as a measure.

If you're interested...

https://1drv.ms/u/s!AnF6rI36HAVkhPIouUq80dpJUlOV5w?e=XUbYYb