Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
alejopiero
Frequent Visitor

Date filter doesn't work in Calculate function

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

 

 

 

 
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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/

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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/

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

 

 
nandukrishnavs
Community Champion
Community Champion

@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
🙂


Regards,
Nandu Krishna

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors