Sum sales by customers with individual Start - End Date

[VuongLM93]

I have a few tables:

1.  Dim - Customers  ( with start - end date) 

CustomerCode	CustomerName	Start Date	End Date
C001	A	1-1-2021	30-1-2022
C002	B	1-10-2020	31-10-2022
C003	C	1-10-2020	31-10-2022
C00N	D	31-10-2022	31-10-2022

2.  Fact - Volume  quantity

CustomerCode	SKU	Volume quantity	Date invoice
C001	SKU 1	10	1-1-2021
C001	SKU 2	20	1-1-2021
....	...	30
C00N	SKU 2	40

How to sum the volume for each Customer Code within their respective Start -End date? 

CustomerCode	CustomerName	Start Date	End Date	Total volume within period
C001	A	1-1-2021	30-1-2022	?
C002	B	1-10-2020	31-10-2022
C003	C	1-10-2020	31-10-2022
C00N	D	31-10-2022	31-10-2022

]

[Anonymous · Accepted solution]

Hi @VuongLM93 

I created a measure that may meet your needs .

Total volume within period

= CALCULATE(SUM('Fact - Volume  quantity'[Volume quantity]),

FILTER('Fact - Volume  quantity','Fact - Volume  quantity'[CustomerCode]=MAX('Dim - Customers'[CustomerCode])

                                                    && 'Fact - Volume  quantity'[Date invoice]>=MAX('Dim - Customers'[Start Date])

                                                     && 'Fact - Volume  quantity'[Date invoice]<=MAX('Dim - Customers'[End Date])))

The quantity within the period will be summed.

The effect is as shown:

Best Regards

Community Support Team _ Ailsa Tao

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

[amitchandak]

@VuongLM93 , A new column in the customer table

sumx(filter(Volume, Volume[Date invoice] >= Customers[Start Date] && Volume[Date invoice] <= Customers[End Date] && Volume[CustomerCode] = Customers[CustomerCode]),Volume[Volume])

[VuongLM93]

Thanks @amitchandak  ,

Unfortunately I cannot create Customer Column due to it being a live dataset,

IS there a way to create a Measure instead?

[amitchandak]

@VuongLM93 , Try a measure like

calculate(sumx(values(Volume[CustomerCode]),Volume[Volume]),filter(Volume, Volume[Date invoice] >= min(Customers[Start Date]) && Volume[Date invoice] <= max(Customers[End Date]) && Volume[CustomerCode] = max(Customers[CustomerCode])))