Forum Discussion
Multiple Filters with SUMX
Hi There,
I hope that I have described this correctly but here it goes:
I'm working on a solution to find previous year-to-date and I found that Kasper On BI had a great solution. I have a new dataset which includes much higher details on my sales data, down to individual lines and then BOM levels of our product build up. Here is an example of the dataset:
| OrderNumber | LineItem | Product | BOM_ID | LineItemPrice | InvoiceDate |
| 400001 | 7 | 60000010 | 0 | 1952.03 | 03/18/2016 |
| 400001 | 7 | 10000010 | 1 | 1952.03 | 03/18/2016 |
| 400001 | 7 | 10011 | 2 | 1952.03 | 03/18/2016 |
| 400001 | 7 | 11000 | 3 | 1952.03 | 03/18/2016 |
| 400001 | 7 | 14015 | 4 | 1952.03 | 03/18/2016 |
| 400001 | 9 | 60000010 | 0 | 863.53 | 03/18/2016 |
| 400001 | 9 | 10000010 | 1 | 863.53 | 03/18/2016 |
| 400001 | 9 | 10011 | 2 | 863.53 | 03/18/2016 |
| 400001 | 9 | 11000 | 3 | 863.53 | 03/18/2016 |
It is a bit cumbersome but I am able to find my order value with the following measure:
$OrderValue = SUMX(FILTER(OrderTable,BOM_ID=0),LineItemPrice))
When I use the attempt to use Kasper's Sales sameperiod measure:
Sales sameperiod =
VAR startyear =
STARTOFYEAR ( ‘Calendar'[Date] ) – 365
VAR enddate =
LASTDATE ( Sales[Date] ) – 365
RETURN
CALCULATE (
SUM ( Sales[sales] ),
DATESBETWEEN ( ‘Calendar'[Date], startyear, enddate )
)
I am having trouble returning a value over that time period that takes into account both the date filter and to just select BOM_ID=0.
Thanks for any help you can give!
6 Replies
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008Thanks Greg!
I have been working on a solution with CALCULATETABLE although I haven't quite solved it yet. I will keep plugging away and let you know how this goes.
Adam
Hi calerof ,
Thank you very much! That worked 🙂
I can honestly say I'm not quite sure why and if you have time, I would love to know. Again thanks so much.
Adam
Hi AdmanPowerBI ,
First and foremost, if you review the pbix file, a Calendar table was added to the model and marked as Date Table.
Then, in the Order Value measure I used an explicit measure to sum the line item price, instead of using the implicit measure LineItemPrice.
Lastly, in the Sales Same Period measure I used the Order Value measure in the RETURN calculation, instead of SUM ( Sales[sales] ).
That was pretty much it.
Best regards,
Fernando
