Forum Discussion

StephenClarke's avatar
StephenClarke
Frequent Visitor
3 years ago
Solved

Dynamically filter last row per item

My 'Fact_Booking' table holds a log of every time a [Sales_ID] changes. I therefore have multiple rows per [Sales_ID] as sales are confirmed / revised / cancelled etc.   I would like to be able to ...
Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    You could try

    Sum of last rows =
VAR ChosenDate =
    MAX ( 'Date'[Date] )
VAR SummaryTable =
    CALCULATETABLE (
        INDEX (
            1,
            'Fact booking',
            ORDERBY ( 'Fact booking'[Last modified date], DESC ),
            PARTITIONBY ( 'Fact booking'[Sales ID] )
        ),
        'Date'[Date] <= ChosenDate
    )
VAR Result =
    SUMX ( SummaryTable, 'Fact booking'[Sales value] )
RETURN
    Result

    This makes a few assumptions. Firstly that your date table is linked to your fact table on the last modified date. Secondly, you will need a unique identifier for each row in fact booking. If you don't have one naturally you can add an index column using power query. Make sure that the unique column is marked as the key column in the modelling view, that will stop INDEX complaining that the table may have duplicates.

johnt75's avatar
johnt75
Super User
3 years ago

You could try

Sum of last rows =
VAR ChosenDate =
    MAX ( 'Date'[Date] )
VAR SummaryTable =
    CALCULATETABLE (
        INDEX (
            1,
            'Fact booking',
            ORDERBY ( 'Fact booking'[Last modified date], DESC ),
            PARTITIONBY ( 'Fact booking'[Sales ID] )
        ),
        'Date'[Date] <= ChosenDate
    )
VAR Result =
    SUMX ( SummaryTable, 'Fact booking'[Sales value] )
RETURN
    Result

This makes a few assumptions. Firstly that your date table is linked to your fact table on the last modified date. Secondly, you will need a unique identifier for each row in fact booking. If you don't have one naturally you can add an index column using power query. Make sure that the unique column is marked as the key column in the modelling view, that will stop INDEX complaining that the table may have duplicates.