Show latest month value in Power BI Card

[Anonymous]

I haven't found a workable solution to this.  Although this link was promising I couldn't get the answer to work.

https://community.powerbi.com/t5/Desktop/Latest-Month-value/m-p/383443#M174525

What I want to do is very similar.  I want to create a card that simply shows the last month of data available for that value.  Say it's Invoiced Sales.  In the above link I got the first part to work create a column with: 

MonthDate = DATE(YEAR([DateField]), MONTH([DateField]), 1)

but when I attempted to create the measure I get errors.  It doesn't seem to like the "END" and I'm not a good enough DAX Coder to see what the problem is.

Last Month Sum = VAR endDate = LASTDATE('YourTable'[MonthDate])
END
CALCULATE(
	SUM('YourTable'[BudgetValue]),
	'YourTable'[MonthDate] = endDate
)

The error I get is:  

The syntax for 'END' is incorrect. (DAX(VAR endDate = LASTDATE('DIMCalendar'[MonthDate])ENDCALCULATE( SUM('YourTable'[BudgetValue]), 'YourTable'[MonthDate] = endDate))).

[OwenAuger · Accepted solution]

Hi @Anonymous

In the Last Month Sum measure, change END to RETURN

This seems to be a typo in the post you referred to.

In general, a sequence of variable declarations with VAR must be followed by RETURN <expression>. 

Regards,

Owen

[jdbuchanan71 · Accepted solution]

Messed with it a bit to get one that doesn't need the MonthDate column in the date table.

MTD Sales = 
VAR MaxDate = MAX ( 'Date'[Date] )
VAR SOMMaxDate = DATE ( YEAR ( MaxDate ) , MONTH ( MaxDate ) ,1 )
VAR EOMMaxDate = EOMONTH( MaxDate , 0 )

RETURN
CALCULATE( SUM('YourTable'[BudgetValue]) ,  
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] >= SOMMaxDate && 'Date'[Date] <= EOMMaxDate) )

[OwenAuger · Accepted solution]

Hi @Anonymous

In the Last Month Sum measure, change END to RETURN

This seems to be a typo in the post you referred to.

In general, a sequence of variable declarations with VAR must be followed by RETURN <expression>. 

Regards,

Owen