Forum Discussion
Helper IICalculate Order Value Total ($) When Containing Item used in Filter - Duplicate Order #'s in system
Community Supporthi, USABB_Data
If you try this formula:
Measure = VAR _orders=VALUES(Orders[OrderId]) return CALCULATE(SUM(Orders[Sales]),FILTER(ALL(Orders),Orders[OrderId] in _orders))
If not your case, please show more expected output with different scenario.
Best Regards,
Lin
Helper IIThis is nearly perfect but now the outstanding issue is the duplicate orders in our system. The latest DAX is providing almost exactly what I need but we have orders with the same Sales Order # in 2015 as 2019 so it's calculating the sum of both orders. This DAX is not including the second SQL query where the date is restricted to >=7/11/2019 to eliminate summing those previous duplicate SO $'s.
- Cmcmahan
Resident Rockstar
Then just add a filter condition for the year to the query.
Measure = VAR _orders=VALUES(Orders[OrderId]) return CALCULATE(SUM(Orders[Sales]),FILTER(ALL(Orders),Orders[OrderId] in _orders && Orders[Date]>=DATE(2019,7,11)))
- USABB_Data
Thanks that does work but I want to avoid hardcoding dates because I want the measure to be reausable. I have the date filter already on my report as well.
- Cmcmahan
So then replace the hardcoded value with a measure or expression that returns the date from your date filter.
Measure = VAR _orders=VALUES(Orders[OrderId]) VAR _curDate = SELECTEDVALUE(Orders[Date]) RETURN CALCULATE(SUM(Orders[Sales]),FILTER(ALL(Orders),Orders[OrderId] in _orders && Orders[Date]>=_curDate))
