VAR RETURN ALL

[Anonymous]

Hello everyone,

I have a page filter to show only current active contracts information. However, I would like to add a column to calculate total sales which includes expired contacts. Hence, I used the following formula:

Total Sales ALL=

VAR Active Contract Sales= CALCULATE([Total Sales],DATESBETWEEN('Calendar'[Date],date (start date)), date (end date)))

RETURN CALCULATE(Active Contract Sales, ALL('Contract Details'[Active Status]))

From the formula above, the result I got is without the sales from expired contract (i.e. same as the result using Active Contract Sales= CALCULATE([Total Sales],DATESBETWEEN('Calendar'[Date],date (start date)), date (end date)))

However, when i tried to separate the VAR RETURN formula into two different measures as below, i managed to get the sales from both active and expired contracts:

Measure 1= CALCULATE([Total Sales],DATESBETWEEN('Calendar'[Date],date (start date)), date (end date)))

Measure 2= CALCULATE([Measure 1], ALL('Contract Details'[Active Status]))

Anyone knows what is wrong with my VAR RETURN formula please?

Thank you!

[wdx223_Daniel · Accepted solution]

variance can not be used in the first parament of CALCULATE. variance only be calculated once, after calculating, its value became solid, can never be changed.

[Anonymous]

Thanks for the reply both @wdx223_Daniel  @Anonymous  😀

[Anonymous]

Hello @Anonymous 

Could you please share the pbix file after removing the sensitive information?