Sum if years from two tables equal each other

In my fact table, I have an OrderDate.

My Date table is linked to my fact table at the OrderDate level.

I have a dimension table of CustomerInfo which contains a customer creation date. This is linked to my fact table on CustomerID.

I am trying to calculate the sales only in which the YEAR of the OrderDate (from fact table) is equal to the YEAR of the CustomerCreationDate (from CustomerInfo table).

Any suggestions? I unfortunately can’t share a workbook.

Thanks!

This is how I tried to approach the measure.

Hello @acnyc88,

Thank You for posting your query onto the Forum.

As mentioned by you - “I have a dimension table of CustomerInfo which contains a customer creation date. This is linked to my fact table on CustomerID.

And you want to analyse the Sales figure based on the YEAR in which the Order Date (Year) = Customer Creation Date (Year). You just need to add another column i.e. YEAR in your CustomerInfo dimension table. Below is the screenshot provided for the reference.

Now, we’ll create a virtual relationship using the “TREATAS” function to analyze the sales figure where Order Date (Year) = Customer Creation (Year). Below are the screenshots provided for the reference -

Sales as per Customer Creation Year - 3

The formulas are written as follows -

Sales as per Customer Creation Year 1 = 
CALCULATE( 
    SUMX( Sales , Sales[Order Quantity] * Sales[Unit Price] ) , 
    TREATAS( VALUES( Dates[Year] ) , Customers[Year] ) )

Or

Sales as per Customer Creation Year 2 = 
CALCULATE( [Total Sales] , 
    TREATAS( VALUES( Dates[Year] ) , Customers[Year] ) )

And then using the below below you can also fix the grand totals -

Sales as per Customer Creation Year 1 (Totals) = 
SUMX(
    SUMMARIZE( Dates , Dates[Year] , "Totals" , [Sales as per Customer Creation Year 1] ) , 
[Totals] )

I’m also attaching the Excel as well as PBIX file of my working for the reference.

Please Note: Also I’m providing a link below of a video which was created by one of our expert @BrianJ about “How To Mask The Sensitive Data”. If this solution doesn’t meet your requirements then please provide a Mock-up file alongwith the solution that you’re looking for.

Hoping you find this useful. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Testing.xlsx (235.9 KB)

Sales as per Customer Creation Year.pbix (114.7 KB)

2 Likes

Thanks very much for the reply! Was hoping to avoid the calculated column, but this makes sense. This solution below also ended up working (Customers = Partners FYI). Thanks for the heads up on the masking sensitive data too. Will use that next time if needed!

Hello @acnyc88,

You’re Welcome. :slightly_smiling_face:

I’m glad I was able to help you.

Thanks and Warm Regards,
Harsh

Hi @acnyc88

I know its already solved, but I just wanted to share my idea

Sales as per Cust Creation Year

@Rajesh Can you please confirm why you used EARLIER in CALCULATE?

Hi @AntrikshSharma

For each date I’m applying filter context on Customers year

Here Outer table is Dates and Inner table is Customers.

Inside calculate I’m using customer table in filter context , to filter customer year i need a value from outer table Dates.

To get the value from outer evaluation I used EARLIER

EARLIER

Earlier

We can avoid earlier, please find below

1 Like