Forum Discussion
Calculate days from filter, overlapping date ranges
I have a table that has a task start date and and task end date, and the number of hours of work spent during that time.
I am trying to allow the user to select a Beginning date and end date, and have Power BI calculate the hours spent during the selected date range. (assuming linear work) grouping by some other column on the tasks. I am having trouble figuring out the proper approach to take towards this. I found an algorithm to calculate if the task range is within the selected date range, but cannot figure out how to get the value from the slicer into a column to calculate the number of days in the task that are within the range.
Hi mhortman,
According to your description, you want to get the datediff in the selected data range, right?
If this is a case, you can refer to below formulas to achieve your requirement.
Logic :
1. If selected start date larger than current start date, use selected start date to calculate.
2. If selected end date less than current end date, use selected end date to calculate.
3. If selected start date large then current end date or selected end date less than current start date, remove.1. Use start date and end date to build a calendar table.
CALENDAR = CALENDAR(FIRSTDATE('Table'[StartDate]),LASTDATE('Table'[EndDate]))2. Write a measure to filter range and calculate the dynamic date.
Diff = var start_Date=FIRSTDATE(ALLSELECTED('CALENDAR'[Date])) var end_Date=LASTDATE(ALLSELECTED('CALENDAR'[Date])) var current_Start=MAX('Table'[StartDate]) var current_end=MAX('Table'[EndDate]) return IF(current_end>start_Date&¤t_Start<end_Date,DATEDIFF(MAX(start_Date,current_Start),MIN(end_Date,current_end),DAY),BLANK())3. Add measure to display the calculated date range.
calculate_start = var start_Date=FIRSTDATE(ALLSELECTED('CALENDAR'[Date])) var current_Start=MAX('Table'[StartDate]) return IF([Diff]<>BLANK(),MAX(current_Start,start_Date)) calculate_end = var end_Date=LASTDATE(ALLSELECTED('CALENDAR'[Date])) var current_end=MAX('Table'[EndDate]) return IF([Diff]<>BLANK(),MIN(current_end,end_Date))If above not help, please share some detail contents.
Regards,
Xiaoxin Sheng
1 Reply
Hi mhortman,
According to your description, you want to get the datediff in the selected data range, right?
If this is a case, you can refer to below formulas to achieve your requirement.
Logic :
1. If selected start date larger than current start date, use selected start date to calculate.
2. If selected end date less than current end date, use selected end date to calculate.
3. If selected start date large then current end date or selected end date less than current start date, remove.1. Use start date and end date to build a calendar table.
CALENDAR = CALENDAR(FIRSTDATE('Table'[StartDate]),LASTDATE('Table'[EndDate]))2. Write a measure to filter range and calculate the dynamic date.
Diff = var start_Date=FIRSTDATE(ALLSELECTED('CALENDAR'[Date])) var end_Date=LASTDATE(ALLSELECTED('CALENDAR'[Date])) var current_Start=MAX('Table'[StartDate]) var current_end=MAX('Table'[EndDate]) return IF(current_end>start_Date&¤t_Start<end_Date,DATEDIFF(MAX(start_Date,current_Start),MIN(end_Date,current_end),DAY),BLANK())3. Add measure to display the calculated date range.
calculate_start = var start_Date=FIRSTDATE(ALLSELECTED('CALENDAR'[Date])) var current_Start=MAX('Table'[StartDate]) return IF([Diff]<>BLANK(),MAX(current_Start,start_Date)) calculate_end = var end_Date=LASTDATE(ALLSELECTED('CALENDAR'[Date])) var current_end=MAX('Table'[EndDate]) return IF([Diff]<>BLANK(),MIN(current_end,end_Date))If above not help, please share some detail contents.
Regards,
Xiaoxin Sheng