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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
marcritchie
Frequent Visitor

Average Distinct Count Over a set time period (ignoring date filter)

Hi there,

 

I have a report where I want to have a dynamic distinct count of users who have booked time to a project within the last month of a certain date. This is the information that I want to show with the Department Size being the dynamic count and the Capacity worked out from that:

Screenshot 2022-01-24 at 09.08.27.png

 

The report has a filter on the reporting date (a Week Commencing date which is in a date table). The relationship is shown here:

Screenshot 2022-01-24 at 09.13.17.png

What I want to show is basically the average distinct count of the Employee ID over the previous 4 weeks.

The problem I have is that the slicer causes the result of my measure to be fixed over time whereas I want to see over time the change in this average based on the previous 4 weeks of wherever it lies on the timeline.

 

As you can see here the Capacity (which uses the Calculated Department Size measure) remains constant over time (as I think it will always pick up the reporting date selected in the slicer). What I want is e.g. 5th December should work out the average over its previous 4 weeks (14th Nov-5th Dec), not the 4 weeks up to the reporting date. This chart isn't connected to the Reporting Date slicer but defaults to last 2 months.

Screenshot 2022-01-24 at 09.20.48.png

 

This is where I am with the DAX measure:

Calculated Department Size = 
CALCULATE (
    DISTINCTCOUNT ( Timesheet[Employee ID] ),
    ALL ( 'Reporting Date'[Week Commencing] ),
    Timesheet[Booked Hours] > 0,
    'Reporting Date'[Week Commencing]
        > MAX ( 'Reporting Date'[Week Commencing] ) - 28,
    'Reporting Date'[Week Commencing] < MAX ( 'Reporting Date'[Week Commencing] )
)

I don't know whether one measure can do the job or if I need to set up one to cover the reporting date and one to cover the trend over time. 

 

Thanks,

Marc

2 REPLIES 2
v-rongtiep-msft
Community Support
Community Support

Hi @marcritchie ,

Please have a try.

Change all to allselected.

Calculated Department Size = 
CALCULATE (
    DISTINCTCOUNT ( Timesheet[Employee ID] ),
    ALLSELECTED ( 'Reporting Date'[Week Commencing] ),
    Timesheet[Booked Hours] > 0,
    'Reporting Date'[Week Commencing]
        > MAX ( 'Reporting Date'[Week Commencing] ) - 28,
    'Reporting Date'[Week Commencing] < MAX ( 'Reporting Date'[Week Commencing] )
)

 

If I have misunderstood your meaning, please provide your pbix file without privacy information.

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

amitchandak
Super User
Super User

@marcritchie , In case you can have week rank in date or week yeat tbale

 

a new column

Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

 

 

CALCULATE(AverageX(values('Date'[Year Week]) ,calculate(DISTINCTCOUNT ( Timesheet[Employee ID] ))), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

 

Rolling 4 = CALCULATE(AverageX(values('Date'[Year Week]) ,calculate(DISTINCTCOUNT ( Timesheet[Employee ID] ))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-28,DAY))

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors