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
banthorpe
Helper I
Helper I

Calculate days in dataset after timeslicer filter

I have a report where I am changing the various views of the data by using a timeslicer. The dataset is a table of meeting information - starttime, endtime etc. from which I calculate utilization, length etc. If I have a meeting that lasts 12 hours each day, when I calculate utilization of the room (as a percentage over a 12 hour period) it should always be 100%. Hwoever, when the the timeslicer selects 2 or more days, the utilization is multiplied by the number of days - so 2 days where the meeting lasted 12 hours would report as 200% usage whereas what I want is it to report 100% usage over those 2 days. To do this, I need to get a variable which holds the number of days in the period selected by the timeslicer so I can divide the utilization % by the number of days. How do I do this? i.e. how to programiatically (in DAX) get the daterange selected by the time slicer?

 

This data is pulled from SQL and the report is setup as an import report.

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@banthorpe,

 

You may refer to DAX below.

Measure =
COUNTROWS ( ALLSELECTED ( 'Table'[Date] ) )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@banthorpe,

 

You may refer to DAX below.

Measure =
COUNTROWS ( ALLSELECTED ( 'Table'[Date] ) )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AlB
Super User
Super User

Hi @banthorpe

 

MAX('Date' [FullDate]) - MIN( 'Date' [FullDate]) +1

 

should do it. Where 'Date' [FullDate] is the base column with the date in your 'Date' table. 

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.