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

[harsha_9640]

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.

@Ritaf1983 

@lbendlin 

@amitchandak 

@Anonymous 

@littlemojopuppy 

@mattiasdesmet 

@Roy_Verharen 

@bcdobbs 

@Aleksandra_MLT 

@TomMartens 

@Ahmedx 

@Anonymous 

@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 pbix file 

[mattiasdesmet]

Hi, does this help?

Solved: Days between based on slicer - Microsoft Fabric Community

[harsha_9640]

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.

[littlemojopuppy]

@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

[Anonymous]

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.