Using Min and Max Dates for a Summarize Calculation

[Anonymous]

I am trying to get a slicer based on time periods.   I know this works, but I really want "Overall" to be period between Min Start Date from a Project Table's Start Date and the Max End Date from the Project Tables End Date.   How do I fit that into this type of table:

I need min and max because I am doing the report based on a Release that has multiple projects.

Date Period =

UNION (

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Project Date Range' , DATESBETWEEN('Project Date Range'[Date],TODAY()-90+1,TODAY()) ), 'Project Date Range'[Date]),"Period","Last 90 Days") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Project Date Range' , DATESBETWEEN('Project Date Range'[Date],TODAY()-30+1,TODAY()) ), 'Project Date Range'[Date]),"Period","Last 30 Days") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Project Date Range'), 'Project Date Range'[Date]),"Period","Overall")

)

[edhans]

Here is your measure that is a bit easier to read:

Date Period =
UNION (
    ADDCOLUMNS (
        SUMMARIZE (
            CALCULATETABLE (
                'Project Date Range',
                DATESBETWEEN ( 'Project Date Range'[Date], TODAY () - 90 + 1, TODAY () )
            ),
            'Project Date Range'[Date]
        ),
        "Period", "Last 90 Days"
    ),
    ADDCOLUMNS (
        SUMMARIZE (
            CALCULATETABLE (
                'Project Date Range',
                DATESBETWEEN ( 'Project Date Range'[Date], TODAY () - 30 + 1, TODAY () )
            ),
            'Project Date Range'[Date]
        ),
        "Period", "Last 30 Days"
    ),
    ADDCOLUMNS (
        SUMMARIZE (
            CALCULATETABLE ( 'Project Date Range' ),
            'Project Date Range'[Date]
        ),
        "Period", "Overall"
    )
)

Where exactly are you wanting to add MIN/MAX? You can use MIN/MAX on date columns, but you cannot use measures for slicers, so you will not be able to use this for a slicer.

If you have a slicer with dates and you want the range of dates, use FIRSTDATE(ALLSELECTED(Date[Date])) and LASTDATE(ALLSELECTED(Date[Date])) to get the first/last date in the date range of the slicer.

If that isn't what you want, can you be more specific?

[Anonymous]

I have attached a picture.  I have a Projects table with Start Date and End Date.   I want my DatePeriod Slicer Overall Category be the min and max of those dates in the bottom right section so that when I select Overall above - it will adjust the Release Burn Up chart.

[edhans · Accepted solution]

There are a few ways to attack this, but the easiest and most straightforward is to:

1. Create a calculated column in your date table that returns TRUE or FALSE depending on if it is between the Start Date and End Date. Pseudocode: ValidDate = IF(Date[Date] <= StartDate && Date[Date] >= EndDate,True,False)
2. Drop your Date[Date] field into a slicer, but filter the slicer to only include dates where ValidDate = True.

You can see this concept explained in depth here, but can easily be applied to your situation.