Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 Date | End Date |
7/1/2016 | 7/3/2016 |
7/6/2016 | 7/12/2016 |
7/15/2016 | 8/14/2016 |
8/15/2016 | 9/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??
Solved! Go to Solution.
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.
And MyTestFactTable like below.
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] ) )
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] ) )
Regards
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.
And MyTestFactTable like below.
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] ) )
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] ) )
Regards
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
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
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 Date | End Date |
4/1/2016 | 6/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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |