Finding last completed month latest non blank value

[AdityaPowerBI]

Hi All,

I am working on a report where I need to fetch last value of previous month based on calendar. If current month is June it should fetch May's last value.  Currently I am using this calculation. Based on the day of the last date I fetch the last working date.  Since the working days are Monday to Friday. If the last date is Sunday or Saturday I fetch last friday else the last working day.

Last Month End =

SWITCH (

[Last Month End Day],

1, ( EOMONTH ( TODAY (), -1 ) - 2 ),

7, ( EOMONTH ( TODAY (), -1 ) - 1 ),

( EOMONTH ( TODAY (), -1 ) )

)

Value LM =
CALCULATE (
SUM ( Amount),
FILTER (
'Table',
'Table'[Date] = [Last Month End]
)
)

But the issue with this logic is if last working day(between Monday - Friday) is a holiday then it gives blank values. Is there a way I can fetch the last non blank value. Based on the below data if current month is June I want output as 200.

May Data
27th May	100
28th May	200
31st May	BLANK(because of holiday)

[v-stephen-msft · Accepted solution]

Hi @AdityaPowerBI ,

Try this measure

Last Month End =
VAR _date =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            [Value] <> BLANK ()
                && [Date] <= EOMONTH ( TODAY (), -1 )
        )
    )
RETURN
    CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', [Date] = _date ) )

Best Regards,

Stephen Tao

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

[amitchandak]

@AdityaPowerBI ,  Try a measure like

maxx(filter(Table, not(isblank(Table[Data])) && eomonth(Table[date],0) = eomonth(today(),-1)),Table[Date])