Find latest item in the same group with an earier date

Question

I have a Power Pivot model with Project and Date tables linked in a relationship:

Project Step Date
------- ---- ----
   A     1   2-Jan-18
   A     2   4-Jan-18
   B     1   3-Jan-18
   B     2   5-Jan-18

Date
----
1-Jan-18
2-Jan-18
3-Jan-18
4-Jan-18
5-Jan-18
6-Jan-18

I trying to develop a pivot table which produces the following output:

           A  B
1-Jan-18
2-Jan-18   1
3-Jan-18   1  1
4-Jan-18   2  1
5-Jan-18   2  2
6-Jan-18   2  2

How can a Measure be written to achieve this?

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

1

FORMULA METHOD

Use the following formula in the first cell of the table your want to create and copy down and to the right. Update your reference ranges before you copy and pay attention to the $ in the formula

=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($C$3:$C$6)/((F$2=$A$3:$A$6)*($E3=$C$3:$C$6)),1)),IF(ROW(F3)=ROW($F$3),"",F2))

Aggregate performs array like operations without being an array. As such ranges inside the AGGREGATE function should not be full column references. If you notice your system slowing down on calculations it may be because your are making a lot of calculations within the aggregate function.

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 20 '18 at 05:35

Forward Ed

9,484
3
22
52

Thank you - but I'm looking for a DAX solution. I'll remove excel-formula from the tags... +1 for your time! – TDP Feb 20 '18 at 08:48
Apologies I went with the Excel Formula tag which you have since removed – Forward Ed Feb 20 '18 at 11:05

score 0 · Accepted Answer · answered Feb 20 '18 at 13:17

I got there in the end:

CurrentStep:=
CALCULATE (
    FIRSTNONBLANK( 'Poject'[Step], 1 ),
    TOPN(
        1,
        CALCULATETABLE(
            'Project',
            FILTER( 
                ALL( Date[Date] ), 
                'Date'[Date] <= MAX( Date[Date] )
            )
         ),
         'Project'[Date],
         DESC
    )
)

Find latest item in the same group with an earier date

2 Answers2

FORMULA METHOD