Dax Formula not working

Hi  homelander123 

Welcome to the Microsoft Fabric Forum. Also, thank you samratpbi  for your quick response. 
 

The issue with your DAX formula arises from how filter logic is applied within the CALCULATE function. Specifically, the expression:

Query1[CSMDate] >= LastYearSameMonthStart && Query1[CSMDate] <= LastYearSameMonthEnd

does not behave as intended within CALCULATE() unless it is wrapped in a FILTER() function. This is because CALCULATE expects a table expression for filtering, not just a Boolean condition.

While I may not have full visibility into the specific structure of your dataset, I have created a sample .pbix file to demonstrate one possible approach to implementing the desired logic.

Consider the below DAX:

CMLYNAACount =
VAR SelectedMonth = MAX(Query1[CSMDate])
VAR LastYearSameMonthStart = EOMONTH(SelectedMonth, -13) + 1
VAR LastYearSameMonthEnd = EOMONTH(SelectedMonth, -12)

RETURN
CALCULATE(
    SUM(Query1[NACCount]),
    FILTER(
        ALL(Query1),
        Query1[CSMDate] >= LastYearSameMonthStart &&
        Query1[CSMDate] <= LastYearSameMonthEnd
    )
)

 
I have  included relevant screenshot and attached the .pbix file for your reference.Please take a moment to review them and see if this solution aligns with your requirements.

If this information helps resolve your issue, kindly consider marking this response as the Accepted Solution, as it may assist other community members facing similar challenges.

 Thank you!