Calculate table: Last record of day for that subscription_id (Dax)

[michellepace]

Hello there. I would like to use DAX to calculate a new table from the below - basically retaining all the yellow rows.  The logic: For each subscription_id, keep its last record for the day (because that will tell me if it's currently on or off).  I've been struggling with this for hours, I'd really appreciate your help. 

Input: this entire table

Output (via calculated table): Only the yellow rows

Logic: For each subscription_id, keep its last record for the day

[michellepace]

Hello @tamerj1 and @OwenAuger . Thank you both very much for your reply. I ended up finding this little youtube video to show me how to do this in Power Query (without having to write code). I'm still really struggling with DAX, although @tamerj1 I could follow your code. And now that I have my (snapshot) table... I need to count the number of subcriptions and can't avoid DAX any longer. I'll post a new thread for this. Thank you very much once agian. 

[tamerj1 · Accepted solution]

Hi @michellepace 
You may also try

Table 2 =
FILTER (
    'Table',
    'Table'[datetime]
        = CALCULATE (
            MAX ( 'Table'[datetime] ),
            ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[subscription_id] )
        )
)

[OwenAuger]

Hi @michellepace 

This should do the trick (existing table referred to as YourTable )

NewTable =
CALCULATETABLE (
    YourTable,
    INDEX (
        1,
        SUMMARIZE (
            YourTable,
            YourTable[subscription_id],
            YourTable[Date],
            YourTable[datetime]
        ),
        ORDERBY ( YourTable[datetime], DESC ),
        DEFAULT,
        PARTITIONBY ( YourTable[Date], YourTable[subscription_id] )
    )
)

Use INDEX to select the latest datetime per combination of subscription_id & Date.

Regards