Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I am trying to find number of days between two dates based on a slicer.
I have a table visual which shows the data based on given date slicer.
I have a date column called "Sampled_date", "Template" which has 260000 rows. I am using a slicer which has "Sampled_date" and passing the dates 6/1/2023 to 9/8/2023. I need to find the number of days between given dates in a slicer. I have the Template ID's which are having data only for some dates. But i need to get 99 in each and every case in the table regardless of the data present for the dates. For suppose if i apply the slicer and see the result i am getting 6 as data for one "Template" is having only from 6/1/2023 to 6/6/2023 but i need to see 99 even in that case also.
I tried this formula but it didn't work.
Days =
var startdate = MIN([sampled_date])
var enddate = MAX([sampled_date])
return DATEDIFF(startdate,enddate,DAY)
This formula is taking only min and max for the template data based on dates and showing result but i need to get from 6/1/2023 to 9/8/2023 as 99 for every record.
Please suggest me a solution.It's urgent.
@Anonymous
@Anonymous
Hi @harsha_9640
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
HI @Ritaf1983
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
6 | |
4 | |
2 | |
2 |
User | Count |
---|---|
4 | |
3 | |
3 | |
3 | |
3 |