topic Re: Calculate table: Last record of day for that subscription_id (Dax) in DAX Commands and Tips

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

michellepace — Sun, 20 Aug 2023 06:58:30 GMT

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

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

OwenAuger — Sun, 20 Aug 2023 08:35:35 GMT

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

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

tamerj1 — Sun, 20 Aug 2023 10:41:40 GMT

Hi michellepace
You may also try

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

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

michellepace — Mon, 21 Aug 2023 06:27:07 GMT

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.