Return the Last Date with Data by Group

[EnrichedUser]

Hi, 

I have a simple model with two tables.

Table A: Receipts
Table B: Date

I am wanting to write a measure that will return the Last Year Month Number that had a receipt for each month of the year. 

As an example
For Key AA, Feb 2023 the expected output is 24276 which is the prior year month of January where we had transactions in the receipt table.

Last Month with Receipts = 
VAR CurrentSiteItemKey = SELECTEDVALUE('Receipts'[Site_Item_Key])
VAR CurrentYearMonthNumber = SELECTEDVALUE('Date'[Year Month Number])

VAR LastMonthWithReceipts =
    CALCULATE(
        MAX('Date'[Year Month Number]),
        FILTER(
            ALL('Receipts'),
            'Receipts'[Eff Date] <= MAX('Date'[Date]) &&
            'Receipts'[Site_Item_Key] = CurrentSiteItemKey
        )
    )
RETURN
    IF( 
        ISBLANK(LastMonthWithReceipts),
        1, 
        LastMonthWithReceipts 
    )

Site_Item_Key	Year Month	Year Month Number	Last Unit Cost Month (current)	Expected	Receipt Data
AA	Jan-23	24276	24276	24276	Yes
AA	Feb-23	24277		24276
AA	Mar-23	24278	24278	24278	Yes
AA	Apr-23	24279		24278
AA	May-23	24280		24278
AA	Jun-23	24281	24281	24281	Yes
AA	Jul-23	24282	24282	24282	Yes
AA	Aug-23	24283	24283	24283	Yes
AA	Sep-23	24284		24283
AA	Oct-23	24285	24285	24285	Yes
AA	Nov-23	24286		24285
AA	Dec-23	24287		24285
BB	Jan-23	24276
BB	Feb-23	24277
BB	Mar-23	24278
BB	Apr-23	24279
BB	May-23	24280
BB	Jun-23	24281	24281	24281	Yes
BB	Jul-23	24282	24282	24282	Yes
BB	Aug-23	24283		24282
BB	Sep-23	24284		24282
BB	Oct-23	24285		24282
BB	Nov-23	24286		24282
BB	Dec-23	24287		24282

[v-rongtiep-msft · Accepted solution]

Hi @EnrichedUser ,

Please have a try.

Create a measure.

Measure =
VAR _3 =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Site_Item_Key] = SELECTEDVALUE ( 'Table'[Site_Item_Key] )
                && 'Table'[Year Month] < SELECTEDVALUE ( 'Table'[Year Month] )
                && 'Table'[Last Unit Cost Month (current)] <> BLANK ()
        ),
        'Table'[Year Month]
    )
VAR _1 =
    IF ( _3 = BLANK (), MAX ( 'Table'[Year Month] ), _3 )
VAR _1re =
    CALCULATE (
        MAX ( 'Table'[Last Unit Cost Month (current)] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Site_Item_Key] = SELECTEDVALUE ( 'Table'[Site_Item_Key] )
                && 'Table'[Year Month] = _1
        )
    )
RETURN
    IF (
        MAX ( 'Table'[Last Unit Cost Month (current)] ) <> BLANK (),
        MAX ( 'Table'[Last Unit Cost Month (current)] ),
        _1re
    )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@EnrichedUser , You can try this approch

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

or try LASTNONBLANKVALUE

calculate(LASTNONBLANKVALUE(Date[Date],SUM(Table[cost])), filter(all('Date'),'Date'[Year]= Max(Date[Year]) ))

Power BI closingbalancemonth closingbalancequarter closingbalanceyear, lastnonblankvalue- Closing Balance, Distibutre Target: https://youtu.be/yPQ9UV37LOU

DAX functions: lastnonblankvalue , firstnonblankvalue: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=26940s