Forum Discussion
Date filter doesn't work in Calculate function
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/
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.