Search code examples
powerbidaxpowerpivotssas-tabular

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?

Solution

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