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 -

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.

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.

Thanks and Warm Regards,
Harsh

Hi @acnyc88

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

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

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

We can avoid earlier, please find below

2 Likes