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
Hanuma_Srikiran
Frequent Visitor

Get start and end date from Timeline Slicer

Let me keep it simple. I need to find the number of days within a Date range. Say my date range is from 07/01/2016 to 09/30/2016. My table consists of data for certain dates alone:

Initial Rental Start DateEnd Date
7/1/20167/3/2016
7/6/20167/12/2016
7/15/20168/14/2016
8/15/20169/14/2016

Now if I am to select date range from 07/01/2016 to 09/30/2016 in the Time Slider and also have to find the difference of these two dates (in days), but I don't have this {Start Date} and {End Date} available in my table. Is there any way to do this or a work around??

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

@Hanuma_Srikiran



Now if I am to select date range from 07/01/2016 to 09/30/2016 in the Time Slider and also have to find the difference of these two dates (in days), but I don't have this {Start Date} and {End Date} available in my table. Is there any way to do this or a work around??

You can use MIN and MAX function to get the Start Date and End Date from Timeline Slicer. See my sample below.

 

I assume you have Date table called MyDateTable contains a date column which is used by Timeline Slicer like below.

date.PNG

And MyTestFactTable like below.

 fact.PNG

Then should be able to use the formula below to get the Start Date and End Date from Timeline Slicer, and calculate the difference of them in days.

StartDate = CALCULATE ( MIN ( MyDateTable[Date] ), ALLSELECTED ( MyDateTable[Date] ) )

EndDate = CALCULATE ( MAX ( MyDateTable[Date] ), ALLSELECTED ( MyDateTable[Date] ) )

DateDifff =
CALCULATE (
    DATEDIFF ( MIN ( MyDateTable[Date] ), MAX ( MyDateTable[Date] ), DAY ),
    ALLSELECTED ( MyDateTable[Date] )
)

result.PNG

And use the formula below to calculate the utilization.

Utilization =
DATEDIFF (
    MIN ( MyTestFactTable[Initial Rental Start Date] ),
    MAX ( MyTestFactTable[End Date] ),
    DAY
)
    / CALCULATE (
        DATEDIFF ( MIN ( MyDateTable[Date] ), MAX ( MyDateTable[Date] ), DAY ),
        ALLSELECTED ( MyDateTable[Date] )
    )

r2.PNG

 

Regards

View solution in original post

5 REPLIES 5
v-ljerr-msft
Employee
Employee

@Hanuma_Srikiran



Now if I am to select date range from 07/01/2016 to 09/30/2016 in the Time Slider and also have to find the difference of these two dates (in days), but I don't have this {Start Date} and {End Date} available in my table. Is there any way to do this or a work around??

You can use MIN and MAX function to get the Start Date and End Date from Timeline Slicer. See my sample below.

 

I assume you have Date table called MyDateTable contains a date column which is used by Timeline Slicer like below.

date.PNG

And MyTestFactTable like below.

 fact.PNG

Then should be able to use the formula below to get the Start Date and End Date from Timeline Slicer, and calculate the difference of them in days.

StartDate = CALCULATE ( MIN ( MyDateTable[Date] ), ALLSELECTED ( MyDateTable[Date] ) )

EndDate = CALCULATE ( MAX ( MyDateTable[Date] ), ALLSELECTED ( MyDateTable[Date] ) )

DateDifff =
CALCULATE (
    DATEDIFF ( MIN ( MyDateTable[Date] ), MAX ( MyDateTable[Date] ), DAY ),
    ALLSELECTED ( MyDateTable[Date] )
)

result.PNG

And use the formula below to calculate the utilization.

Utilization =
DATEDIFF (
    MIN ( MyTestFactTable[Initial Rental Start Date] ),
    MAX ( MyTestFactTable[End Date] ),
    DAY
)
    / CALCULATE (
        DATEDIFF ( MIN ( MyDateTable[Date] ), MAX ( MyDateTable[Date] ), DAY ),
        ALLSELECTED ( MyDateTable[Date] )
    )

r2.PNG

 

Regards

BhaveshPatel
Community Champion
Community Champion

What do you mean by this.

 

"but I don't have this {Start Date} and {End Date} available in my table.". Do you want to analyze data for those null dates as well. 

 

Make it bit clear.

 

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

I mean I don't have 07/01/2016 in my Start Date column and 09/30/2016 in my End Date column. Whenever a user slides over the Timeline slider to select a date range, these two date values changes. I need to use these two dates as an input for my calculated field.

It would be better if you use parameters for this purpose. It is dynamic and can adapt to your requirement.

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Consider the below to be a sample start and end date for a product that is rented out in the format MMDDYY.

Initial Rental Start DateEnd Date
4/1/20166/30/2016
  

 

Till now we have been generating report through a tool which will accept the start date and end date from the user and give us a pdf of the details.

 

Consider I would like to generate a report from Jan 1st to August 31st. In which I want to find out what is the utilization of the product. The time the product is rented out is utilized and the time it is at the store is unutilized. In the above case out of the 8months(Jan 1st to August 31st) the product is utilized for 3 months from 1st April to 30th June(which is 3 months). Now the utilization is 3/8 = 0.375. Right now I have two plots one is the slicer to choose the date and I want another bar chart or gauge meter to give me the utilization. I'm not able to decide which field should I give for the slicer, should it be rental start date or rental end date because these are the only 2 date feilds I have. I am stuck at the point because I don't find a way to tell power BI to detect the start date of my report(Jan1st - user input) and minus it from the rental start date(1st April) and add it to the end date of my report(31st August- user input) minus the rental end date(June  30th) which will give me the unutilized time period. I know that for any application development we can have a date picker which will accept the report start date and end date from the user and then do the calculations. I suppose this explanation would have given a clear picture of my challenge

 

Thanks 

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.