cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

Compare dates on one dimension table to dynamic report filter dates from date dimension

I am trying to find a way to dynamically determine whether a Sales Rep was active on any given day. The data model I am currently using has a Sales Rep dimension "dim_rep" and a Date dimension "dim_dates" that are not immediately related in any way. Both of these dimensions are connected to multiple fact tables (sales calls & customer appointments).

 

On dim_rep, there are two date columns in field date (the date the rep went out in to the field) and termination date (the date they left the company). Sales Reps that are still with the company have a termination date of 1/1/1900 and Sales Reps in training that have not gone out into the field yet have an in field date of 1/1/1900. 

 

The report I am working on has relative date filtering using the "dim_dates" date1 column. Users should be able to select multiple timeframes in the report.

 

Ideally, I should be able to determine how many active Sales Reps I had in the field at any point in time. These would be Sales Reps that meet the below criteria:

  • Termination Date = 1/1/1900 OR Termination Date >= dynamic minimum date1 filter
  • In Field Date <> 1/1/1900 AND In Field Date <= dynamic maximum date1 filter

This is where I'm currently at, but any date1 references do not recognize report filter contexts

CALCULATE (
    COUNT ( 'dim_rep'[Rep] ),
    FILTER ( 'dim_rep', 'dim_rep'[Job Title] = "Account Executive" ),
    FILTER (
        'dim_rep',
        'dim_rep'[Termination Date] = DATE ( 1900, 1, 1 )
            || 'dim_rep'[Termination Date]
                >= 'dim_dates'[date1]
    ),
    FILTER ( 'dim_rep', 'dim_rep'[In Field Date] <> DATE ( 1900, 1, 1 ) ) ,
    FILTER ( 'dim_rep', 'dim_rep'[In Field Date] < 'dim_dates'[date1] )
)

Any attempts at MIN( ) or MAX( ) functions on 'dim_dates'[date1] simply return the absolute minimum or maximum on the dim_dates table rather than whatever range date1 has been filtered to.

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: Compare dates on one dimension table to dynamic report filter dates from date dimension

Hi @CRamirez 

 

you might consider creating pbix file that will contain some sample data, upload the pbix to onedrive or dropbox and share the link to the file. Please do not forget to describe the expected results based on this sample data.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Highlighted
Advocate II
Advocate II

Re: Compare dates on one dimension table to dynamic report filter dates from date dimension

@v-diye-msft,

Thank you for your response. Below is the link to the sample data model/report.

https://app.powerbi.com/groups/me/reports/3405b211-5d72-424b-87d5-b84f42b3e6bf?ctid=39d34f0b-2985-47... 

 

What I'd like to be able to do is have a column or measure on dim_rep that states whether a rep is considered active for the dates selected for date1. For the example, this screenshot of the report below:sample.pngThe Reps would looks like this:

excel-sample.png

 

 

 

 

 

 

Also, if I were to change the relative date1 filter to "This Month" rather than "This Year" Rep BBXJ would switch from TRUE to FALSE

 

Let me know if the link doesn't work

Highlighted
Community Support
Community Support

Re: Compare dates on one dimension table to dynamic report filter dates from date dimension

Hi @CRamirez 

 

I can't access your report. Could you please kindly share your dummy pbix? that I can draw it up for you.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors