cancel
Showing results for
Did you mean:
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, 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?

5 REPLIES 5
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!
Helper II

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

Community Support

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.
Super User

@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!
Helper II

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.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!