Forum Discussion
VAR RETURN ALL
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:
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?
Anyone knows what is wrong with my VAR RETURN formula please?
Thank you!
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.
3 Replies
- wdx223_Daniel
Community Champion
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.
Thanks for the reply both wdx223_Daniel Anonymous 😀