Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CRamirez
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
v-diye-msft
Community Support
Community Support

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.

@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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.