Date filter not working in Calculate

[elimey]

Hello dear fellowe PowerBIers, 

I need you help pelalse. 
I have the following Dax formula: 

But the part that I highlighted, that filters the Maturity date , doesn't work. Therefore, nothing comes out of the calcualtion. 
Can someone please help? 

What an I doing wrong?

[Anonymous · Accepted solution]

Thanks for the reply from saud968 , please allow me to provide another insight: 
Hi  @elimey ,

You can try the following DAX: The integrity of your original code is equivalent to adding FILTER(ALL('table)). The ALL function ignores filters. You might consider FILTER('table), which creates a temporary table in the current evaluation context, containing rows that meet the filter criteria. Other rows will be empty and not counted.

Test =
VAR _currentficalyear =
    YEAR ( TODAY () + IF ( MONTH ( TODAY () ) >= 4, 1, 0 ) )
VAR _nextfiscalyear = _currentficalyear + 1 
VAR _currenfiscalquarter =
    SWITCH (
        TRUE (),
        MONTH ( TODAY () ) >= 4
            && MONTH ( TODAY () ) <= 6, 1,
        MONTH ( TODAY () ) >= 7
            && MONTH ( TODAY () ) <= 9, 2,
        MONTH ( TODAY () ) >= 10
            && MONTH ( TODAY () ) <= 12, 3,
        MONTH ( TODAY () ) >= 1
            && MONTH ( TODAY () ) <= 3, 4
    ) 
VAR _begin =
    SWITCH (
        _currenfiscalquarter,
        1, DATE ( _nextfiscalyear - 1, 4, 1 ),
        2, DATE ( _nextfiscalyear - 1, 7, 1 ),
        3, DATE ( _nextfiscalyear - 1, 10, 1 ),
        4, DATE ( _nextfiscalyear - 1, 1, 1 )
    ) // 2024.10.1
VAR _end =
    SWITCH (
        _currenfiscalquarter,
        1, DATE ( _nextfiscalyear - 1, 6, 30 ),
        2, DATE ( _nextfiscalyear - 1, 9, 30 ),
        3, DATE ( _nextfiscalyear - 1, 12, 31 ),
        4, DATE ( _nextfiscalyear - 1, 3, 31 )
    ) 
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[AccountShort] = "Long"
                && 'Table'[Date] >= _begin
                && 'Table'[Date] <= _end
        )
    )

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[saud968]

Try to simplify the measure

CALCULATE (

   SUM ( 'Market_Values_Vain'[Balance] ),

   'Market_Values_Vain'[MaturityDate] >= BeginningOfCurrentQuarterNextFY

)

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[elimey]

I tried, but still not going through. 
I also tried to work wit hthe ALL() function

[saud968]

let's verify if `BeginningOfCurrentQuarterNextFY` and `EndOfCurrentQuarterNextFY` are calculating as expected. You can create temporary measures to output these values and check them

Measure_BeginningOfNextFY = BeginningOfCurrentQuarterNextFY
Measure_EndOfNextFY = EndOfCurrentQuarterNextFY

Add these measures to your Power BI table or a card visual and confirm that they return correct start and end dates for the next fiscal quarter.

Use DATE Function for Comparisons
Ensure that `MaturityDate` is in a comparable date format. If `Market_Values_Vain[MaturityDate]` is in a different format, it may not work directly in comparisons. You can use the `DATE()` function or `DATEVALUE()` to explicitly cast your `MaturityDate`:

CALCULATE (
SUM ( 'Market_Values_Vain'[Balance] ),
'Market_Values_Vain'[MaturityDate] >= DATEVALUE(BeginningOfCurrentQuarterNextFY) &&
'Market_Values_Vain'[MaturityDate] <= DATEVALUE(EndOfCurrentQuarterNextFY),
'Market_Values_Vain'[AccountNameShort] = "Long"
)

Use Variables for Readability
Break down your code with variables to ensure each part is working as expected. For example, create variables for the date comparisons:

VAR BeginNextFY = BeginningOfCurrentQuarterNextFY
VAR EndNextFY = EndOfCurrentQuarterNextFY

RETURN
CALCULATE (
SUM ( 'Market_Values_Vain'[Balance] ),
'Market_Values_Vain'[MaturityDate] >= BeginNextFY &&
'Market_Values_Vain'[MaturityDate] <= EndNextFY,
'Market_Values_Vain'[AccountNameShort] = "Long"
)

This way, you can more easily debug the issue.

Final DAX Formula After Adjustments revised DAX formula might look like:

VAR BeginNextFY = BeginningOfCurrentQuarterNextFY
VAR EndNextFY = EndOfCurrentQuarterNextFY

RETURN
CALCULATE (
SUM ( 'Market_Values_Vain'[Balance] ),
NOT ISBLANK('Market_Values_Vain'[MaturityDate]),
'Market_Values_Vain'[MaturityDate] >= BeginNextFY &&
'Market_Values_Vain'[MaturityDate] <= EndNextFY,
'Market_Values_Vain'[AccountNameShort] = "Long"
)

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!