Forum Discussion
filter expression in Calculate function is not getting evaluated.
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")/1000000where 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
3 Replies
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
