cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leont3
Helper II
Helper II

Dynamic Dates in a Matrix

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, WeekSales
2021 - W133
2021 - W144
etc.. 
2021 - W275

 

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?

5 REPLIES 5
amitchandak
Super User
Super User

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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 IDHire Date
12327 September 2020
45627 March 2020

 

Sales

Employee IDSales AmountSale Date
1231003 March 2020
123101 March 2021
456530 September 2020
123103 December 2020

 

Let me know if there is anything else I can do to explain my answer. Thanks again

Hi @leont3,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@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 

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.