Hi,
I am trying to do what I thought to be a simple calculation at first, but it is giving me trouble.
I have a fact table with data on various projects, first I need to calculate completed projects all time to date value in like this (Calendar below is pointing to my delivery date):
SQM Total Completed =
IF (
MIN ( 'Calendar'[Date] )
<= CALCULATE ( MAX ( PlotFact[Delivery Date] ); ALL ( PlotFact ) );
CALCULATE (
[SQM Total Sum];
FILTER (
ALL ( PlotFact );
YEAR ( PlotFact[Delivery Date] ) <= YEAR ( MAX ( 'Calendar'[Date] ) )
)
)
)
In a similar manner I need to calculate projects under construction all time to date, BUT this calculation takes into account a second date "Construction Begin Date" also in the fact table.
Instead of the first calculation the new one now is defined as summing the value of all projects to date with delivery year > context based year AND construction begin date <= context based construction begin year.
I would assume that this would work, but....
SQM Total U/C =
IF (
MIN ( 'Calendar'[Date] )
<= CALCULATE ( MAX ( PlotFact[Delivery Date] ); ALL ( PlotFact ) );
CALCULATE (
[SQM Total Sum];
FILTER (
PlotFact;
YEAR ( PlotFact[Constroction Begin Date] ) <= YEAR ( MAX ( 'Calendar'[Date] ) )
&&
YEAR ( PlotFact[Delivery Date] ) > YEAR ( MAX ( 'Calendar'[Date] ) )
)
)
)
... it returns blank for all years, which is definetly not the case.