Forum Discussion
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
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/
4 Replies
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 🙂nandukrishnavsHi! This works fine!! thanks a lot!! Hugs.
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/
