Difficulties using VAR with a simple measure

[alecsonline]

Hi all,

I am having not the expected result with this:

this formula works:

booked =
var readingdatevar=[maxreadingdate]
return
CALCULATE(SUM(bookings[roomnumbers]);FILTER(booking[reservationdate]<readingdatevar))

I thought I could write the same this way:

booked =
var readingdatevar=[maxreadingdate]
var reserved= SUM(bookings[roomnumbers])
return
CALCULATE(reserved;FILTER(bookings;booking[reservationdate]<readingdatevar))

This second version is not working. I have no mistakes (powerBI accept the formula), but the resulting values are wrong e they do not change when the "readingdatevar" changes....

Any idea whiy?

Thank you all!

Alessandro

[Anonymous]

Hi @alecsonline

Could you please share a sample file with mentioned calculation. By looking formula its seems to be fine.

Best Regards,

Ravi

[alecsonline]

Yes sure, this is the table I take the data from (a simplified version). For each reservation there is one line for each day of stay; also reservation date is displaied.

1 more table for the reading dates that is a simple calendar; in fact I need to look at this data taking care of the reservation date, i.e. if looking at the "reading date" 15/07/2018 it should give me the total of the rooms that were already booked at that date.

The "date" column is linked to a simple calendar table; the "reservationdate" column is linked to the "reading dates" table.

The first formula gives me the desired results.

Tks!

[Zubair_Muhammad · Accepted solution]

@alecsonline

VARIABLES are just a store of value. They do not store Expressions. So They are not dynamic like MEASURES

So Once you define a VARIABLE it is stored as a VALUE
So you are evaluating a fixed number when you are evaluating a VARIABLE

i.e. something like this

CALCULATE(150000;FILTER(bookings;booking[reservationdate]<readingdatevar))

[alecsonline]

Thank you Zubair, it was a concept I was completely missing! In the meanwhile I found this in an article about Variables: 

"Other Important Points: Each of the variables are evaluated once prior to the commencement of the RETURN portion of the formula."

That seems to be coincident with your as I would translate it with: anything you do BEFORE the "return" will be not modified later.

Thanks a lot!

[Anonymous]

Hi @alecsonline

With Your provided sample i have done the same calculation and in both way the are same.

Could you please see and revert me is that your requirement or something diffrent.

I am attaching pbix file too with name Booking.pbix

Thanks and Regards,

Ravi

[Anonymous]

https://drive.google.com/open?id=1_BZoPbxQD69ThrtQwvolskf3t2mP1nb0

[alecsonline]

Thank you Ravik for your gift! It is true it works, but not when I modify it for my needings... my fault because I would not even be able to write them in a clear way. But your way to use the VAR is very useful and it is really a great help!

Alessandro