I have a report showing the number of weekly sales by employee seperated by week in a matrix. The report has been set up so the end user can only look at one sales person at a time (this is done using a filter visual). The matrix must only show sales data for the past 6 months of the sales person's anniversary date (one year after hire date).
For example: If Bob was hired 27 September 2020, the matrix will only show the number of sales for the dates between 27 March 2021 to 27 September 2021 (6 months before).
In the case of Bob his sales will look like:
|2021 - W13||3|
|2021 - W14||4|
|2021 - W27||5|
The problem I am having is that my matrix is showing all of the dates in my date table which means I am getting records outside of the date range. Because the dates are dynamic and are different among each sales person, I am unable to use a simple date filter on the visual.
The data model looks like this:
I have established the following measures based on the hire date of the employee information table:
Period Start Date
Period Start Date = EDATE([Anniversary Date], -6)
Anniversary Date = EDATE(MAX('Employee Information'[Hire Date]), 12)
Number of Sales
Number of Sales = VAR EndDate = [Anniversary Date] VAR StartDate = [Period Start Date] RETURN CALCULATE( COUNTROWS('Sales'), 'Sales'[Sale Date] >= StartDate, 'Sales'[Sale Date] <= EndDate)
The matrix is built by using the following fields:
How would I go about in only showing the relevent dates in a matrix based on the period start date and the anniversary date?
@leont3 , if an employee is joined with sales on employee ID, then that employee will not have a record outside its work duration. Not very clear
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi @amitchandak , thanks for your reply. Unfortunately, I am not allowed to share any data at all considering the sensitive nature of the data. What I can share with you is some dummy data and information which is hopefully enough...
So the data model consists of three tables:
These are what each table consist of:
|Employee ID||Hire Date|
|123||27 September 2020|
|456||27 March 2020|
|Employee ID||Sales Amount||Sale Date|
|123||100||3 March 2020|
|123||10||1 March 2021|
|456||5||30 September 2020|
|123||10||3 December 2020|
Let me know if there is anything else I can do to explain my answer. Thanks again
I'd like to suggest you create user mapping(username and user table records) and setting dynamic RLS with the current user to achieve your requirement:
If the above does not help, can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
@leont3 , Ideally speaking month, qtr or year. From date table should be used
and sum(Sales[Sales Amount]) as measure should do .
Employee taable should only be joined with sales with 1-M , where employee is on one side.
We need only create a measure to use dates from the employee there is sales outside the employment date.
We can create a new column in fact too
var _1 = countx(filter(employee, sales[sales date] >= employee [Hire Date] && sales[sales date] >= employee [tremination Date] ), employee[Employee ID])
if(isblank(_1), false(), True())
You can use this flag as filter
Sorry, I don't think I've explained myself properly and this is not exactly what I was after...
I am after only sales within the last 6 months of an employee's anniversary date, this does not seem to work and I do not have termination date in my records.
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.