Dax measure: SUM exclude rows based on combination of 2 columns

[BobKoenen]

Hi Al

I want to create a sum formula but exclude certain rows. The data is a large fact table of milions of records so I do not want to create a new column for this summation. 

Name	date	amount
John	31-12-2020	20
John	1-1-2020	50
sue	1-2-2020	30

I want to have a sum of all values exept where Name is John and the date is after 31-12-2020. So I want to exclude John from the calculation for all rows with date after 31-12-2020

Desired result in above example = 50. 

[v-deddai1-msft · Accepted solution]

Hi @BobKoenen ,

If I get your requirements well, you can use the following measure:

Measure = CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[Name]<>"John" && 'Table'[date]>=DATE(2020,12,31)))+ CALCULATE(SUM('Table'[amount]),'Table'[date]<DATE(2020,12,31))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[jaideepnema]

@BobKoenen 

You can use a measure like this :

Amount Measure = CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[Name]="John" && 'Table'[date]>DATE(2019,12,31)))

Please accept this as a solution if your question has been answered !!

Appreciate a Kudos 😀

[BobKoenen]

Hi Unfortunatly this does not work 

I want to exclude all amounts for John after 31-12-2020. So I adopted the formula

Measure = CALCULATE(SUM(table[amount], FILTER(table, table[name] <> "John" && Table[Date]>DATE(2019,12,31)). 

But this exlcudes ALL rows before the date, not only the rows of JoHN