Dax Formula not working

[homelander123]

CMLYNAACount =

VAR SelectedMonth = MAX(Query1[CSMDate])

VAR LastYearSameMonthStart = EOMONTH(SelectedMonth, -12) + 1

VAR LastYearSameMonthEnd = EOMONTH(SelectedMonth, -12)

RETURN

CALCULATE(

    SUM(Query1[NACCount]),

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

)
I am trying to get a count based on a filter however, i am not getting any results on this query. Can you advise whats wrong?
I am trying to get NAC Count based off the date slicer for last year current month . e.g if slicer says november 2024 this field should show november 2023

[v-karpurapud]

Hi @homelander123 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud · Accepted solution]

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!

[samratpbi]

Hi,

though I am not sure about the data, however I think you need to make the below change:

VAR LastYearSameMonthStart = EOMONTH(SelectedMonth, -13) + 1

EOMONTH returns last date of the month. If you are doing -12 then + 1, besically its returning 1st day of the following month, which makes your month start date greater than month end date. I think that is the reason you are not getting any values.

If this helps to resolve your problme, then please mark it as solution, thanks - Samrat