Dynamic Dates in a Matrix

[Anonymous]

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: 

Month, Week	Sales
2021 - W13	3
2021 - W14	4
etc..
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:

• Date Table
• Sales Table (connected to date table via 'sales date')
• Employee Information Table (connected to sales table based on Employee ID)

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

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:

• Date Hierarchy (containing Year, Month and Week)
• Number of Sales as shown above

How would I go about in only showing the relevent dates in a matrix based on the period start date and the anniversary date?

[amitchandak]

@Anonymous , 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.

[Anonymous]

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:

• Date Table (active relationship connected to sale date)
• Employee Information (connected to sales table, one to many via employee ID)
• Sales Table (connected to employee info, and date table)

These are what each table consist of:

Employee Info

Employee ID	Hire Date
123	27 September 2020
456	27 March 2020

Sales

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

[v-shex-msft]

Hi @Anonymous,

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:

Solved: RLS with UserName() - Microsoft Power BI Community

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.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , 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 

Flag =

var _1  = countx(filter(employee, sales[sales date] >= employee [Hire Date] && sales[sales date] >= employee [tremination Date] ), employee[Employee ID])

return 

if(isblank(_1), false(), True())

You can use this flag as filter

[Anonymous]

@amitchandak 

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.