Get last available date for certain category

2
votes

I have a table (Table1) in Power Pivot which consists of values with dates and category assigned to them (non-unique):

CAT      DATE         VALUE
A        2015-01-03   12
A        2015-01-07   2
B        2015-03-14   50

Then, in another table I have the same categories with dates:

CAT      DATE         
A        2015-01-04   
A        2015-01-06   
A        2015-01-08   
B        2015-03-16

In Table2 I want to get value from Table1 corresponding to latest date lower or equal than date from Table2 in current row for that category, i.e.:

CAT      DATE         VALUE_FROM_TABLE_1
A        2015-01-04   12 (value from 2015-01-03)
A        2015-01-06   12 (value from 2015-01-03)
A        2015-01-08   2  (value from 2015-01-07)
B        2015-03-16   50 (value from 2015-03-14)

Alternatively, I need a formula for Table2 to calculate latest date from Table1 which is lower or equal to this from Table2 for category in the current row, like this:

CAT      DATE         LAST_AVAILABLE_LOWER_OR_EQUAL_DATE_FOR_CAT
A        2015-01-04   2015-01-03
A        2015-01-06   2015-01-03
A        2015-01-08   2015-01-07
B        2015-03-16   2015-03-14

I have tried various combinations of LASTDATE and CALCULATE but to no avail. I will be grateful for any hint.

1
excelpowerpivotdax
Self-reply, it is: =CALCULATE ( Lastdate( Table2[DATE] ); FILTER ( ALL (Table2); Table2[DATE] <=[DATE] && Table2[CAT]=[CAT] ) ) – mstefanski1987
If that is the answer, can you add it as an actual answer (down below) and then accept it? This helps those of us looking for unanswered questions to help. – Byron Wall
Sure, I have added the answer and I will accept it tomorrow. Thanks for pointing it out. – mstefanski1987

1 Answers

1
votes

I have figured it out, and it goes like this:

=CALCULATE ( 
    LASTDATE( Table2[DATE] ); 
    FILTER ( 
        ALL (Table2); 
        Table2[DATE] <=[DATE] && Table2[CAT]=[CAT] 
    )
 )