Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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 , 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 🙂
User | Count |
---|---|
72 | |
66 | |
34 | |
25 | |
22 |
User | Count |
---|---|
96 | |
94 | |
59 | |
45 | |
42 |