date variable not filtered in calculate function

[AnSchmi]

Can someone help me to understand why my calculate function is not filtered by a variable containing a date ?

Code working with static date function in calculate:

VAR _MaxDate  =      MAX('f_Transactions'[Date.Transaction])         // Max Date Transactions = 2022.07.08

VAR _MinDate  =      DATE(2022,07,04)                                           // Min Date = Max Date - 30 Days = 2022.07.04

    Return

        CALCULATE(

            [Total Sales],

            FILTER(

                'd_Tbl Calendar',

                And(

                    'd_Tbl Calendar'[Date] <= _MaxDate,

                    'd_Tbl Calendar'[Date] >= _MinDate

                )

            )

        )
Result in Table =

When I replace the static date function by for example

VAR _MinDate =  _MaxDate - 3 
I do not get the table filtered as expeced. Instead I do get all records in table:

What is wrong ? 
Thanks a lot 

[Anonymous · Accepted solution]

Hi @AnSchmi ,

Not sure what the model looks like. The steps of my test are as follows:

Create a date table and create relationships between it and the fact table.

Calendar = CALENDAR(MIN('f_Transactions'[Date.Transaction]),MAX('f_Transactions'[Date.Transaction]))

Create a new measure:

Measure = 
VAR _MaxDate = MAX('Calendar'[Date])
VAR _MinDate = _MaxDate - 3 
VAR _Result = 
CALCULATE(
    [Total Sales],
        FILTER(ALL('Calendar'),
        AND('Calendar'[Date]<=_MaxDate,
        'Calendar'[Date]>=_MinDate)
        )
)
RETURN
_Result

Drag the measures and the date column from calendar table into the table visual:

Filter correctly

The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[AnSchmi]

Dear Gao, 

thanks a lot for your reply. 

Your solution seems to me static as well. 

Nevertheless, thanks a lot for your help. 
Best Regards

Andreas

[AntonioM]

I think the problem is the table is filtering the variable '_MaxDate'. It only gets the max date for the dates it can see, so if you have a row of one single date, that is the max date (instead of 22/07/08) . Try

VAR _MaxDate = CALCULATE ( MAX('f_Transactions'[Date.Transaction]), ALL('f_Transactions') ) 

and see if that works

[AnSchmi]

Hi AntonioM, 
unfortunately not, the result after changing the _MaxDate is a table with only the last record.

[AntonioM]

And that's with _Mindate as _Maxdate - 3?

[AnSchmi]

Dear AntonioM, 

what do you mean by 
_Mindate as _Maxdate - 3 ? 
I do not understand your question. 

Can you be more precice please. 
The code after your recommendiation: 

Sales not Filtered in Calculate() =

    VAR _MaxDate =

        CALCULATE(

            MAX('f_Transactions'[Date.Transaction]),

            All(f_transactions)

                 )                                   // Max Date Transactions = 2022.07.08

    VAR _MinDate =

        _MaxDate - 3                                 // Min Date = Max Date - 30 Days = 2022.07.04

    Return

        CALCULATE(

            [Total Sales],

            FILTER(

                'd_Tbl Calendar',

                And(

                    'd_Tbl Calendar'[Date] <= _MaxDate,

                    'd_Tbl Calendar'[Date] >= _MinDate

                )

            )

        )

= 

Best Regards

Andreas