VAR RETURN ALL

Anonymous — Fri, 12 Nov 2021 06:53:07 GMT

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!

Re: VAR RETURN ALL

Anonymous — Fri, 12 Nov 2021 08:23:18 GMT

Hello Anonymous

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

Re: VAR RETURN ALL

wdx223_Daniel — Fri, 12 Nov 2021 08:58:18 GMT

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.

Re: VAR RETURN ALL

Anonymous — Mon, 15 Nov 2021 09:38:41 GMT

Thanks for the reply both wdx223_Daniel Anonymous 😀

topic Re: VAR RETURN ALL in DAX Commands and Tips

VAR RETURN ALL

Re: VAR RETURN ALL

Re: VAR RETURN ALL

Re: VAR RETURN ALL