Forum Discussion

alecsonline's avatar
alecsonline
Icon for Helper I rankHelper I
8 years ago
Solved

Difficulties using VAR with a simple measure

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

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi alecsonline

     

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

     

    Best Regards,

    Ravi

    • alecsonline's avatar
      alecsonline
      Icon for Helper I rankHelper I
      8 years ago

      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's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

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