How to calculate total sales by customer after a specific date (vary by customer) in diff. table?

[hassanh2]

Hello,

Can you please assist with a DAX function to perform the following - Calculate the sum of sales by customer effective the promotion date (i..e all sales after 01-Mar-22). Note in some cases the promotion date may vary for different customers.

[Anonymous · Accepted solution]

Hi @hassanh2 ,

You can create a measure.

Measure = CALCULATE(SUM('Sales Data'[Amount]),FILTER('Sales Data',[Date]>=MAX('Marketing Campaign'[Date])))

The output is in the lower right corner.

Best Regards,

Stephen Tao

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

[hassanh2]

Thank you Stephen.

[hassanh2]

I was actually using the following code which worked at the customer level, but when I tried to summerise it in a visual to get for example by region -  the "Total" is not correct - I guess the code just aggregate the sales by region from the MaxPromotion date (doesnt add the sales for each customer from the respective promotion date)

CALCULATE(SUM([SALESValue]),FILTER('SALES',[PromotionDate]>=Max('Customer'[PromotionDate])))

[hassanh2]

Thanks @rubayatyasmin , Im getting an error message that:
"The column Customer[PromotionDate] doesnt exist or doesnt have a relationship to any table available in the current context" 
but there is already an active relationship between Customer ID in both tables "Sales[CustomerID]" and "Customers[CustomerID]" !!

[rubayatyasmin]

@hassanh2 do you have a column in the same name as my DAX code? Like PromotionDate? If not then you need to use the correct table. Also, if the code doesn't work after the alteration use the below code. 

Total Sales After Promotion :=
SUMX (
FILTER (
'Sales',
'Sales'[SaleDate] >= RELATED ( 'Promotions'[PromotionDate] )
),
'Sales'[SalesAmount]
)

Remember to replace the table and column names in the formula with the actual table and column names in your Power BI model.

[rubayatyasmin]

Hi, @hassanh2 

Use this code: 

Total Sales Post Promotion :=
CALCULATE(
SUM(Sales[SalesAmount]),
FILTER(
ALL(Sales),
Sales[SaleDate] > RELATED(Customers[PromotionDate])
)
)