cancel
Showing results for
Did you mean:
Highlighted
New Member

## VAR works with a constant date but not in a formula

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?
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

## Re: VAR works with a constant date but not in a formula

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...

______________

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

excelwithallison.com

5 REPLIES 5
Highlighted
Super User IV

## Re: VAR works with a constant date but not in a formula

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...

______________

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

excelwithallison.com

Highlighted
Super User IX

## Re: VAR works with a constant date but not in a formula

@kkondarzewski , 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]))

Proud to be a Super User!

Highlighted
New Member

## Re: VAR works with a constant date but not in a formula

It worked with MAXX

Thanks!

Highlighted
New Member

## Re: VAR works with a constant date but not in a formula

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

Highlighted
Community Support

## Re: VAR works with a constant date but not in a formula

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

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors