Sum if years from two tables equal each other

acnyc88 · August 25, 2020, 8:48pm

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!

acnyc88 · August 25, 2020, 9:18pm

This is how I tried to approach the measure.

Harsh · August 25, 2020, 10:10pm

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.

Thanks and Warm Regards,
Harsh

Testing.xlsx (235.9 KB)

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

acnyc88 · August 25, 2020, 11:02pm

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!