Need to show sum of Sales after Grouping by Customer.

[Anonymous]

I have Date range as a slicer

When I select date I need to show Total Sales.

Data:

Customer	Date	Sales
A	01-Jan	100
A	01-Feb	200
A	01-Mar	300
A	01-Apr	400
B	01-Jan	10
B	01-Feb	20
B	01-Mar	30
B	01-Apr	40

If i Selected Date is between 01 Feb - 01 Apr

Required output:

Customer	Date	SalesSum
A	01-Feb	900
A	01-Mar	900
A	01-Apr	900
B	01-Feb	90
B	01-Mar	90
B	01-Apr	90

If i Selected Date is between 01 Feb - 01 Mar

Customer	Date	SalesSum
A	01-Feb	500
A	01-Mar	500
B	01-Feb	50
B	01-Mar	50

I tried implementing with use of ALLexcept but it is not considering date filter.

Request to please help.

[v-yuta-msft · Accepted solution]

@Anonymous ,

You need to create another table 'Table (2)' with one date column as below and create the slicer based on the date column in the new table:

  

Then create a measure using dax below:

Sales Total = CALCULATE(SUM('Table'[Sales]), FILTER(ALLEXCEPT('Table', 'Table'[Customer]), 'Table'[Date] in VALUES('Table (2)'[Date])))

You can also refer to the pbix.

Community Support Team _ Jimmy Tao

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

[Anonymous]

Thank you very much! It worked as expected 🙂

[Greg_Deckler]

You ALLEXCEPT should look like ALLEXCEPT('Table','Table'[Customer]), does it?

[Anonymous]

Thanks for reply Greg.

I am using 

CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Customer]))

The value is not changing if we change the dateslicer. It is grouping up for each customer though.