Date filter doesn't work in Calculate function

[alejopiero]

Hello, 

I am involved in a simple problem and I am not able to solve that.

I need the SUM function would be filtered by a measure that contains a date, but It doesn't work.

The function is:

Bags YoY = CALCULATE(sum('Sales & Returns'[(*) Sale quantity (bags)]),filter('Sales & Returns','Sales & Returns'[(*) Sales order date (adjusted)]<=[LastOrderDatePY]))

The measure that is the filter takes the last date of other data set and It works right. 

LastOrderDatePY = LASTDATE('Sales orders'[(*) Sales order date])

The result of the function is blank, but If I change the main replacing the measure by the date function, It works fine:

Bags YoY = CALCULATE(sum('Sales & Returns'[(*) Sale quantity (bags)]),filter('Sales & Returns','Sales & Returns'[(*) Sales order date (adjusted)]<=DATE(2019,10,10))

But I need that the date would be updated dynamically taking the last date of the other table.

I am checked that the date format of all columns and measure involved in that have the same date format and they are right.

Could you give me a hand please?.

Thanks in advance.

Alejo

[amitchandak · Accepted solution]

@alejopiero , try like

Bags YoY = CALCULATE(sum('Sales & Returns'[(*) Sale quantity (bags)]),filter(all('Sales & Returns'),'Sales & Returns'[(*) Sales order date (adjusted)]<=[LastOrderDatePY]))

better do with date table

Bags YoY = CALCULATE(sum('Sales & Returns'[(*) Sale quantity (bags)]),filter(all('Date'),'Date'[Date]<=LASTDATE('Date'[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[alejopiero]

@amitchandak Thanks a lot!!! I started to use date table. Hugs

[nandukrishnavs]

@alejopiero 

Try this

Bags YoY =
VAR _lastdate =
    CALCULATE (
        MAX ( 'Sales orders'[(*) Sales order date] )
    )
VAR _result =
    CALCULATE (
        SUM ( 'Sales & Returns'[(*) Sale quantity (bags)] ),
        FILTER (
            'Sales & Returns',
            'Sales & Returns'[(*) Sales order date (adjusted)] <= _lastdate
        )
    )
RETURN
    _result

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[alejopiero]

@nandukrishnavsHi! This works fine!! thanks a lot!! Hugs.