Forum Discussion

AnSchmi's avatar
AnSchmi
Helper II
4 years ago
Solved

date variable not filtered in calculate function

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_Trans...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

AntonioM's avatar
AntonioM
Solution Sage
4 years ago

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's avatar
    AnSchmi
    Helper II
    4 years ago

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

     

    • AntonioM's avatar
      AntonioM
      Solution Sage
      4 years ago

      And that's with _Mindate as _Maxdate - 3?

      • AnSchmi's avatar
        AnSchmi
        Helper II
        4 years ago

        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