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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
harsha_9640
Frequent Visitor

Get number of days based on input from a slicer date range

HI,

I am trying to find number of days based on slicer start date and end date regardless of data.

 

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.

 

@Ritaf1983 

@lbendlin 

@amitchandak 

@Anonymous 

@littlemojopuppy 

@mattiasdesmet 

@Roy_Verharen 

@bcdobbs 

@Aleksandra_MLT 

@TomMartens 

@Ahmedx 

@v-yiruan-msft 

@v-yuezhe-msft 

 

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.sample .png

 

Sample pbix file 

 

4 REPLIES 4
mattiasdesmet
Resolver I
Resolver I

Hi @mattiasdesmet 

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. 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.