filter expression in Calculate function is not getting evaluated.

[Ani26]

Hi Team,

Need your small assistance.
I have one DAX expression as below,

Variance = 
var dyear = [vDefaultYear] 
var dquarter = [vDefaultQuarter] 
RETURN 
CALCULATE(SUM(SalesForce[New Actual]),Fiscal_Calendar[Fiscal_Year] = dyear ,Fiscal_Calendar[Fiscal_Quarter] = dquarter,SalesForce[RecordType] = "Actual", SalesForce[Product Sub Category] <> "ESG - ABF")/1000000

where value of [vDefaultYear] = 2019 and [vDefaultQuarter] = 4

However, the expression only takes into consideration the value of Fiscal_Calendar[Fiscal_Year] = dyear but ignores the value for Fiscal_Calendar[Fiscal_Quarter] = dquarter. But if I enter 4 as the value manually instead of dquarter, the formula gives the exact result. 

The requirement is to show the value only for quarter 4 of 2019, but the above mentioned expression returns value for all the quarters of 2019.
I checked the datatype and everything is matching but still trapped in showing the variance value only for last quarter.

Any help on this would be highly appreciated.

Thanks,

Ani

[Mariusz]

Hi @Ani26 

Why not just use Relationships to filter the tables?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Mariusz]

Hi @Ani26 

Can you share the expression for  [vDefaultQuarter] ?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Ani26]

Hi @Mariusz 

Sure!!
Here it is.

vDefaultQuarter = CALCULATE(MAX(Fiscal_Calendar[Fiscal_Quarter]),FILTER(Fiscal_Calendar,Fiscal_Calendar[Fiscal_Year] = [vDefaultYear] && Fiscal_Calendar[DefaultQuarterSeq] = [vDefaultQuarterSeq]))

vDefaultQuarterSeq = max(Fiscal_Calendar[DefaultQuarterSeq]) 

vDefaultYear = Year(MAX(SalesForce[vFilterDateCalculation]))

Kindly let me know if any additional information required.

Thanks,
Ani