Forum Discussion

Ani26's avatar
Ani26
Icon for Helper III rankHelper III
6 years ago

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")/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

 

 

 

3 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion

    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's avatar
      Ani26
      Icon for Helper III rankHelper III

      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

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion

    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.