Multiple Filters with SUMX

[AdmanPowerBI]

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!

[calerof · Accepted solution]

Hi @AdmanPowerBI ,

Check this pbix file.

Regards,

Fernando

[AdmanPowerBI]

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

[calerof]

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

[AdmanPowerBI]

Again thank you!

[Greg_Deckler]

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-TIT...

[AdmanPowerBI]

Thanks 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