CODEWITHSUNDEEP
powerbidaxpowerpivotssas-tabular
TDP
TDP

Reputation: 1231

Find latest item in the same group with an earier date

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?

Upvotes: 0

Views: 60

Answers (2)

TDP
TDP

Reputation: 1231

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
    )
)

Upvotes: 0

Forward Ed
Forward Ed

Reputation: 9884

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.

POC

Upvotes: 1

Related Questions