Advanced filtering

[newgirl]

Hello

I have a sample data model with my current project like below.

Data tables are:

Activity table - records activities made by salespersons

Creation table - records when a client was created in the database

Sales table - records sales

In a table visual in the report, I need to report per Salesperson, their number of activities, the volume, and accounts created. The tricky part is when I compute for the volume. It needs to capture only the volume of the clients that was created based on the date slicer. 

Right now, it just uses the simple SUM() dax and so it shows as 120 which is wrong, because it's capturing also the 20 volume from client 5002, which was created on Jan 2 (outside the date slicer). What I want displayed is 100 volume only because it comes from client 5001, which was only created in the database on Jan 1, the filtered date slicer

How I think the volume is being capure incorrectly:

How I want it to capture correctly:

[amitchandak]

@newgirl , FOr such a case you have use merge queries in power query  and get creation date of client in sales 

Do not expand take the min/max of the date (Aggregation)

Merge Tables (Power Query) : https://www.youtube.com/watch?v=zNrmbagO0Oo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=16

Or create a new column in DAX in Sales

Minx(filter('Creation Date', 'Creation Date'[Client ID] = sales[Client ID]) ,'Creation Date'[Creation Date] )

Then you can join with sales creation date with date, if join is inactive you can use userelationship  in measure

example

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

Employees as on Date : https://youtu.be/e6Y-l_JtCq4

[newgirl]

Hello @amitchandak !

I tried what you suggested in my actual working file:

This is the relationship:

And this is the new measure (Vol_Cust) 

Volume_related = CALCULATE([Volume],USERELATIONSHIP('Calendar'[Date],'CR tbl_phrawsales'[HisSapZ0123_GEOLOC.CreatedOn]))

I tried to filter how the measure got 45. I understand how it works but...

How can I filter it further to get 22.4? The logic I want to apply is that since the date slicer in the page is filtered to Feb 1 to Feb 28, the measure should calculate for the sum of the volume filtered to the 'CreatedOn' date and the 'Invoice Date'. Hope you can help me how the measure can be improved.

[BrianConnelly · Accepted solution]

Need to update the code as such using variables...

Volume_related = 
VAR startDate = MIN('Calendar'[Date])
VAR endDate = MAX('Calendar'[Date])
RETURN CALCULATE([Volume],USERELATIONSHIP('Calendar'[Date],'CR tbl_phrawsales'[HisSapZ0123_GEOLOC.CreatedOn])
,InvoiceDate >= startDate && InvoiceDate <= endDate
)