Can I create a DATE( ) formula that my viewers can change?

[BW40]

I created a column to filter our my customer IDs using three conditions based on two date fields, [start date] and [end date].

Is there a way to create a formula or DATE field that my viewers can change themselves that I can use in a formula (which will change all of my values on multiple sheets automatically)? My formulas all work as expected but require me to go in and change the two DATE( ) formulas whenever someone wants to look at a different time frame.

I want viewers to be able to adjust the start and end dates but the way my conditions are built, a slicer will not work unless I can put it in a formula (since a slicer only looks between two dates).

The conditons are:

1. [applied] <= [end date]

2. If NOT ISBLANK([service stop date]) && [service stop date] >= [start date] || ISBLANK([service stop date])

3. NOT ISBLANK[approval date]

[devanshi]

IF (CALENDAR[Date] <>BLANK, MAX[StartDate] && MAX[EndDate],[stopservice])

If it is correct accept answer

[BW40]

 @v-shex-msft  I tried to post a mock table last week but it apparently did not go through. It would be easier if we could attach excel files. Please see the sample table below. Some customers can have more than one service but have the same customer ID while those that were not approved still get a customer ID but not a service id.  

@DJSwezey  in my program compromise, I broke it down into several sheets with different slicers but the original goal was to include all of the fields on one sheet. The problem is the totals use the same dates differently, some look at those that closed in a range (could be a slicer) and another looks at those that never closed or closed after a period (a slicer would not let me create an OR ISBLANK condition). 

For anther row, I need people who applied in a date range (can be a slicer) while another looks at those who applied before a certain date (I made a rough version with a slicer but slicers themselvers would not let me do a <= [end date]). I need something similar for when customer applications were approved. Relying on filters is a little less user-friendly for those I am creating this for. It was more efficient when they could just change a start and end date parameter.

Customer	Service ID	Approval date	Start Date	End date	Summary	Count
1	1	10/1/2022	10/1/2022	11/1/2022	Customers	5
2	2	10/1/2022	10/1/2022	1/5/2023	services	7
3					2022 approval	6
4	3	10/1/2022	10/1/2022		2023 approval	1
4	4	11/1/2022	11/1/2022	12/1/2022	2022 services	3
4	5	11/155/2022	11/15/2022	12/16/2022	22/23 services	1
3	9	1/1/2023	1/1/2023	3/6/2023	2023 services	1
2					closed services	5
5	7	11/1/2022	11/1/2022		open services	2

[v-shex-msft · Accepted solution]

Hi @BW40,

It seems like a common ‘start date’, ‘end date’ range analysis requirement, you can refer the Greg’s blog about some common scenario if it helps:

Before You Post, Read This: start/end date 

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @BW40,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[DJSwezey]

Have you tried incorporating two variables for your selected dates in a date slicer?

For example:

VAR minselecteddate =
    MIN ( Calendar[Date] )
VAR maxselecteddate =
    MAX ( Calendar[Date] )

Then you can filter your calculations to be within these two dates.