Lastdate and SUMX

[Patrick20115]

Hi,


I have two tables with property information. Both tables have effective dating (from and to date). They are related through a property dimension table (lookup table).


The first table (PropertyFact) stores the size of the properties and the occupied space. A new record is added when the property size changes, or when the occupancy changes.


The second table (PropertyStatus) stores the status of the properties.


I am interested in calculating the average occupancy. In the pivot table I need to be able to select the data range and the status. The average occupancy should only be calculated for the properties that have the selected status at the last date of the selected date range.


I came up with the following formula for the total occupied area (occupied area * number of days):




Occupied Area :=
CALCULATE (
CALCULATE (
CALCULATE (
SUMX (
PropertyFact,
IF (
VALUE ( LASTDATE ( PropertyStatus[effective_to_dt] ) )
< VALUE ( LASTDATE ( calendar[date] ) )
|| VALUE ( LASTDATE ( PropertyStatus[effective_from_dt] ) )
> VALUE ( LASTDATE ( calendar[date] ) ),
BLANK (),
(
IF (
VALUE ( PropertyFact[EFFECTIVE_TO_DT] )
<= VALUE ( LASTDATE ( calendar[date] ) ),
VALUE ( PropertyFact[EFFECTIVE_TO_DT] ),
VALUE ( LASTDATE ( calendar[date] ) )
)
- IF (
VALUE ( PropertyFact[EFFECTIVE_FROM_DT] )
>= VALUE ( FIRSTDATE ( calendar[date] ) )
&& VALUE ( PropertyFact[EFFECTIVE_FROM_DT] )
>= VALUE ( LASTDATE ( PropertyStatus[EFFECTIVE_FROM_DT] ) ),
VALUE ( PropertyFact[EFFECTIVE_FROM_DT] ),
IF (
VALUE ( FIRSTDATE ( VALUES ( calendar[date] ) ) )
>= VALUE ( LASTDATE ( PropertyStatus[EFFECTIVE_FROM_DT] ) ),
VALUE ( FIRSTDATE ( VALUES ( calendar[date] ) ) ),
VALUE ( LASTDATE ( PropertyStatus[EFFECTIVE_FROM_DT] ) )
)
)
+ 1
)
)
* PropertyFact[PU_OCCUP_NRA_MSMNT]
),
FILTER (
PropertyStatus,
VALUE ( LASTDATE ( PropertyStatus[effective_from_dt] ) )
<= VALUE ( LASTDATE ( calendar[date] ) )
&& VALUE ( LASTDATE ( PropertyStatus[effective_to_dt] ) )
>= VALUE ( LASTDATE ( calendar[date] ) )
)
),
FILTER (
PropertyFact,
VALUE ( PropertyFact[EFFECTIVE_TO_DT] )
>= VALUE ( FIRSTDATE ( VALUES ( calendar[date] ) ) )
&& VALUE ( PropertyFact[EFFECTIVE_FROM_DT] )
<= VALUE ( LASTDATE ( VALUES ( calendar[date] ) ) )
)
),
FILTER (
calendar,
VALUE ( calendar[date] )
>= VALUE ( FIRSTDATE ( PropertyStatus[EFFECTIVE_FROM_DT] ) )
)
)






In the same way I calculate the total property area. Averagee occupancy is the division of the two.


This formula works, unless I pull the time dimension in the pivot table. Let's say I select Q1 2015, but also expand the time dimension to months. The row for January then uses the status at January 31st to calculate the status. However it should always use the status at the last selected date, which in this case is March 31.


Does anyone have an idea how I can get the last selected day regardless other filters?


Thanks,
Patrick

 

[theBardd]

Can you post the workbook, oir at least some sample data from each table?

 

[scottsen]

Heh, funny. I do quite a bit of work for a client doing *exactly* this problem (averge occupancy). I feel like we should be besties

My first question is just "why do you use the VALUE() function LIKE MAD!?". Do you literally have text fields all over the place for some reason!? I actually had to go lookup what the heck VALUE() even DID... I never use it.

I generally try to build my measures up from simpler measures... get's a bit tough to view a big measure like that. Maybe the stuff inside the SUMX expression can go into another measure?

Before I forget, if you google for "DAX Events in Progress" there are a bunch of interesting articles, and this pattern is pretty similiar.

But, I generally agree w/ theBardd... without seeing at least a picture of the model/relationships its tough to wrap your head around what is going on. (I don't quite understand why there are dates on both PropertyStatus and PropertyFact, don't quite understand your question w/ both quarters and months, etc).