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_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 

 

 

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

6 Replies

  • 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.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    • AnSchmi's avatar
      AnSchmi
      Helper II
      4 years ago

      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