alternative to calendar table

[bharosha]

I need a min date and max date that can be used to derive other calculations. Currently I have a calendar table 

Dates = Calendar(Date(1995, 1, 1), today())

I used min date = CALCULATE(Min(Dates[Date]), ALLSELECTED(Dates))

and max date = CALCULATE(Max(Dates[Date]), ALLSELECTED(Dates))

I use date column from dates calendar table to create a date filter and use min value and max value to derive count calculation that changes as I change the date filter. 

The counts and other statistics takes forever to load because of all the date table. Is there an alternative way to do it? date filter whose min value and max value can be used for the calculations.

[_elbpower]

I suggest creating a dedicated 'TimeTable' that includes all relevant dates and incorporating it into your calculations. Below is the revised DAX code:

This approach ensures the availability of a comprehensive 'TimeTable,' enhancing the accuracy and efficiency of your calculations. Replace 'YourMeasureToAggregate' with your specific measure or calculation to customize the code for your exact requirements.

Let's go through a hypothetical example using the provided code and data:

FilteredMeasure =
VAR MinDate = CALCULATE(MIN('TimeTable'[Date]), ALLSELECTED('TimeTable'))
VAR MaxDate = CALCULATE(MAX('TimeTable'[Date]), ALLSELECTED('TimeTable'))

RETURN
CALCULATE(
YourMeasureToAggregate, -- Replace with your actual measure or calculation
DATESBETWEEN(
'TimeTable'[Date],
MinDate,
MaxDate
),
MaxDate - 'Query1'[close_date].[Date] > 0 &&
MaxDate - 'Query1'[close_date].[Date] < MaxDate - MinDate
)

Data:

Minimum Date: 10
Maximum Date: 15
Close Date: 12
Open Date: 9
Calculation:
The aim is to find schools where the difference between the close date and the maximum date is exactly 3.

(Maximum Date - Close Date) = 15 - 12 = 3

Filtering:
We select schools where the condition (Maximum Date - Close Date) equals 3, and this condition is met when it is less than 5 (the difference between the maximum date and the minimum date).

Close Date: 9,
(Maximum Date - Close Date) = 15 - 9 = 6 (Not selected)

Close Date: 15,
(Maximum Date - Close Date) = 15 - 15 = 0 (Not selected)

Close Date: 11,
(Maximum Date - Close Date) = 15 - 11 = 4 (Selected)

I have not included other columns assuming that 'opened' is always supposed to be less than 'max date,' and since 'close' is also less than 'max,' 'open' will be automatically less.

If you provide the meanings of the '[Date All School Closed]' and '[First Date Any School Opened]' columns, we can consider how to incorporate them into the analysis.

[Greg_Deckler]

@bharosha Can you just use MIN('Dates'[Date]) and MAX('Dates'[Date]) ?

[bharosha]

@Greg_Deckler , Even though I changed it to just min and max, the count, average, median still takes forever to load. Is there any alternative to calendar table? Is there any way to use user input date filter which min and max value can be used in the calculation?

[Greg_Deckler]

@bharosha I have my doubts that it is the dates causing a problem here. I mean, 2024 to 1995 would only be approximately 10,585 dates. What does the rest of your formula/formulas look like?

[bharosha]

@Greg_Deckler , below is the formula to count distinct id which is the giving hard time

Schools* = CALCULATE(DISTINCTCOUNTNOBLANK('Query1'[ID]),
FILTER('Query1',
('Query1'[close_date].[Date]>= [MinDate] && 'Query1'[close_date].[Year]>= 2013 || ISBLANK('Query1'[close_date])) &&
'Query1'[open_date].[Date]<=[MaxDate] &&
('Query1'[Date All School Closed].[Date]>= [MinDate] && 'Query1'[Date All School Closed].[Year]>= 2013 || ISBLANK('Query1'[Date All School Closed])) &&
'Query1'[First Date Any School Opened].[Date]<=[MaxDate]))

[Anonymous]

Hi @bharosha ,

Please update the formula of measure [Schools*] as below and check if it can return the expected result...

Schools* =
VAR _seldate =
    SELECTEDVALUE ( Dates[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNTNOBLANK ( 'Query1'[ID] ),
        FILTER (
            'Query1',
            (
                (
                    'Query1'[close_date] >= _seldate
                        && YEAR ( 'Query1'[close_date] ) >= 2013
                )
                    || ISBLANK ( 'Query1'[close_date] )
            )
                && 'Query1'[open_date] <= _seldate
                && (
                    (
                        'Query1'[Date All School Closed] >= _seldate
                            && YEAR ( 'Query1'[Date All School Closed] ) >= 2013
                    )
                        || ISBLANK ( 'Query1'[Date All School Closed] )
                            && 'Query1'[First Date Any School Opened] <= _seldate
                )
        )
    )

If the above ones can't help you get the expected result, please provide some raw data in your table 'Query1' (exclude sensitive data) with Text format and your expected result with special examples and screenshots base on provided sample data.  It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[bharosha]

@VS , It did not work. The output is 0 with this formula. there is no minimum and maximum value here. 

Below would not work because it is calculating off of same value. Please advise. The file is a huge file and full of sensitive data. 

'Query1'[close_date] >= _seldate
                && 'Query1'[open_date] <= _seldate

[Anonymous]

Hi @bharosha ,

Thanks for your feedback. Could you please provide some raw data in your table 'Query1' (exclude sensitive data) with Text format, the calculation logic and your expected result with special examples and screenshots base on provided sample data? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards