Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
harsha_9640
Frequent Visitor

Find Number of days between two dates from a single column based on slicer

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.

 

@Ritaf1983 

@lbendlin 

@amitchandak 

@Anonymous 

@littlemojopuppy 

@mattiasdesmet 

@Roy_Verharen 

@bcdobbs 

@Aleksandra_MLT 

@TomMartens 

@Ahmedx 

@Anonymous 

 

 

2 REPLIES 2
Ritaf1983
Super User
Super User

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

HI @Ritaf1983 

 

Sample Data:

TemplateNo.of Tests
Temp_0119
Temp_0220
Temp_0330
Temp_0432
Temp_0523
Temp_0624
Temp_0756
Temp_0876
Temp_0987

Here is the sample data.

 

Date range from slicer : 06-01-2023 to 09-08-2023 (mm-dd-yyyy format)

 

Expected Outcome

 

TemplateDaysNo.of Tests
Temp_019919
Temp_029920
Temp_039930
Temp_049932
Temp_059923
Temp_069924
Temp_079956
Temp_089976
Temp_099987

 

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.

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.