Including a date filter in a DAX measure

[Anonymous]

Hi all

I am trying to filter a sum of expenses by the item. I have done the following:

= CALCULATE(SUM(costofsales[Cost of Sales Expense]), FILTER(costofsales,costofsales[Cost of Sales Item]="Wages - Trainers Training Delivery (Contract)" || costofsales[Cost of Sales Item]="Training - Venue Hire" || costofsales[Cost of Sales Item]="Training - Resources & Printing" || costofsales[Cost of Sales Item]="Training - Accommodation" || costofsales[Date] > DATE(2021,5,31)))

It is providing me with the incorrect answer. 

I need the filter to contain all of the above sales items but only include those that have a date after 31 May 21. 

Any assistance would be great. 

Thanks. 

Karen

[Jos_Woolley · Accepted solution]

Hi,

Measure =
CALCULATE (
    SUM ( costofsales[Cost of Sales Expense] ),
    costofsales[Cost of Sales Item]
        IN {
        "Wages - Trainers Training Delivery (Contract)",
        "Training - Venue Hire",
        "Training - Resources & Printing",
        "Training - Accommodation"
    },
    costofsales[Date] > DATE ( 2021, 5, 31 )
)

Regards

[Jos_Woolley · Accepted solution]

Hi,

Measure =
CALCULATE (
    SUM ( costofsales[Cost of Sales Expense] ),
    costofsales[Cost of Sales Item]
        IN {
        "Wages - Trainers Training Delivery (Contract)",
        "Training - Venue Hire",
        "Training - Resources & Printing",
        "Training - Accommodation"
    },
    costofsales[Date] > DATE ( 2021, 5, 31 )
)

Regards

[Anonymous]

Hi Jos Thank you. I knew it would be something simple. 

Appreciate your prompt reply. 😀

[Jos_Woolley]

Sure! You're very welcome!

Cheers

[Anonymous]

Hi Jos

IRT my query above, how do I do "does not contain" those fields? Instead of the IN is there another function I use? I have been looking and trying different things but I haven't got it. Are you able to help me out? 

[Jos_Woolley · Accepted solution]

Have you tried combining NOT with IN?

Regards

[Anonymous]

Thank you @Jos_Woolley Tried that and it worked like a charm. Appreciate your guidance with that one. 

Cheers

Karen

[Jos_Woolley]

Great stuff, glad to know! Cheers, Karen.

Jos

[Anonymous]

Yes, but I think I put them in the wrong place/order. I will have another go at that and see how I go. Thank you for the push in the right direction. 

Cheers

Karen