Forum Discussion
Advanced filtering
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:
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 )
3 Replies
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
Employees as on Date : https://youtu.be/e6Y-l_JtCq4
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.
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 )
