VAR works with a constant date but not in a formula

[Anonymous]

I'm already tired searching

This works in dax:

MySales =

   VAR ReportingMonth = month(DATEVALUE("05/01/2020"))

   RETURN CALCULATE(SUM(fDespesas[Dispêndio]);FILTER(dCalendario;MONTH(dCalendario[Date])<=ReportingMonth);KEEPFILTERS(fDespesas[Dispêndio]))

This doens't:

MySales =

VAR ReportingMonth = month(LASTDATE(fDespesas[data]))

RETURN CALCULATE(SUM(fDespesas[Dispêndio]);FILTER(dCalendario;MONTH(dCalendario[Date])<=ReportingMonth);KEEPFILTERS(fDespesas[Dispêndio]))

This "month(LASTDATE(fDespesas[data]))" in a measure returns 5 but using "VAR ReportingMonth = 5" works also.

This variable is accepting only a constant to work fine.

I don't want to put a 5 but want the DAX to calculate that 5.

Any solution?

[amitchandak]

@Anonymous , Try this

MySales =
VAR ReportingMonth = month(maxx(allselected(fDespesas),fDespesas[data]))
RETURN CALCULATE(SUM(fDespesas[Dispêndio]);FILTER(dCalendario;MONTH(dCalendario[Date])<=ReportingMonth);KEEPFILTERS(fDespesas[Dispêndio]))

or

MySales =
VAR ReportingMonth = calculate(month(LASTDATE(fDespesas[data])))
RETURN CALCULATE(SUM(fDespesas[Dispêndio]);FILTER(dCalendario;MONTH(dCalendario[Date])<=ReportingMonth);KEEPFILTERS(fDespesas[Dispêndio]))

[Anonymous]

It worked only with MAXX. Calculate brought the same as month_Lastdate.

[Icey]

Hi @Anonymous ,

Glad to hear the problem has been solved. You may help accept the replies making sense as solution above. Your contribution is highly appreciated.

Best Regards,

Icey

[AllisonKennedy · Accepted solution]

If you try the following measure in your scenario what result do you get?
MySales =
VAR ReportingMonth = month(LASTDATE(fDespesas[data]))
RETURN
ReportingMonth

Not sure if it might work with MAX? https://www.sqlbi.com/blog/marco/2013/10/22/difference-between-lastdate-and-max-for-semi-additive-me...

[Anonymous]

It worked with MAXX

Thanks!