Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
HI,
I am trying to find number of days based on slicer start date and end date regardless of data.
Sample Data:
Template | No.of Tests |
Temp_01 | 19 |
Temp_02 | 20 |
Temp_03 | 30 |
Temp_04 | 32 |
Temp_05 | 23 |
Temp_06 | 24 |
Temp_07 | 56 |
Temp_08 | 76 |
Temp_09 | 87 |
Here is the sample data.
Date range from slicer : 06-01-2023 to 09-08-2023 (mm-dd-yyyy format)
Expected Outcome
Template | Days | No.of Tests |
Temp_01 | 99 | 19 |
Temp_02 | 99 | 20 |
Temp_03 | 99 | 30 |
Temp_04 | 99 | 32 |
Temp_05 | 99 | 23 |
Temp_06 | 99 | 24 |
Temp_07 | 99 | 56 |
Temp_08 | 99 | 76 |
Temp_09 | 99 | 87 |
For example, if i have data for Temp_01 from 06-21-2023 to 06-27-2023 in that case also the "Days" should show 99 instead of 7.Hope that makes sense.
@Anonymous
@Anonymous
Plese refer this file for sampel data. It has only data for one ID and dates from 22-06-2023 to 28-0-2023. So, the no.of days calculation is showing 6 days but in my dataset if i adjust slicer dates from 01-06-2023 to 08-09-2023 whether the data is present or not it should calculate number of days based on slicer date range.
Hi, does this help?
Solved: Days between based on slicer - Microsoft Fabric Community
I don't have a date table. I only have a date column in my dataset and the start date and end date comes from a single column. In the above solution, it has start date and end date as separate columns but in my case both are from same column.
@harsha_9640 please see the documentation about why you should always have a date table
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables
Hi,
I think the solution proposed by @mattiasdesmet makes sense. You could add a date table using DAX Calendar or CalendarAuto function and then create a relationship between the two. Then your slicer can refer the Date field of date table.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
6 | |
6 | |
3 | |
2 | |
2 |
User | Count |
---|---|
6 | |
5 | |
4 | |
4 | |
3 |