Returning values on slicer bounds

[AviramWeiss]

Hello all,

This is a basic question, but I wasn't able to solve it on my own or find a working solution.

I have a table containing date, name and value:

Date	Name	Value
Nov 1, 2022	AAA	20
Nov 2, 2022	AAA	30
Dec 1, 2022	AAA	40
Nov 5, 2022	BBB	10

I also have a date-period slicer, based on the same table. Let's say I slice Nov 1-30, 2022.

I want to show a result grid, with name, sum(value), value @ slicer minimum (in my example Nov 1) and value @ slicer maximum (Nov 30). If values are missing at these particular dates, show either 0 or blank (I don't care):

Name	Total	Start value	End value
AAA	50	20	0 (Or Blank)
BBB	10	0 (Or Blank)	0 (Or Blank)

Obviously, I don't know the slicer choice in advance. It's up to the user to pick...

Tried to create measures with min/max using ALLSELECTED or KEEPFILTERS, and then add columns "First" and "Last" to the table, which are populated only when date=measure. Didn't work.

Ideas would be warmly welcomed.

[FreemanZ]

hi @AviramWeiss 

try to plot a table visual of three measures with the code below:

try like

StartValue =

VAR MinDate = MIN (TableName[Date])

RETURN

CALCULATE(

    SUM(TableName[Value]),

    TableName[Date] = MinDate

)

EndValue =

VAR MaxDate = MAX (TableName[Date])

RETURN

CALCULATE(

    SUM(TableName[Value]),

    TableName[Date] = MaxDate

)

Total =

VAR MinDate = MIN (TableName[Date])

VAR MaxDate = MAX (TableName[Date])

RETURN

CALCULATE(

    SUM(TableName[Value]),

    TableName[Date] >= MinDate

         &&TableName[Date] <= MaxDate

)

[AviramWeiss]

Thank you, FreemanZ, for the quick and thorough response.
I have tried your solution, but like all previous ones, it takes a "personal" max for every category.

Thus, If I pick a min/max date that has no instance, the measure takes the first/last date within the selected range, and not the exact first/last date.
Here's the data I entered again (all dates are dd/mm/yyyy format):

When I pick Nov 2-7, I want to get for AAA startValue=30 and endValue=0, becuase it has no records at Nov 7. These are the results I got:

Thank you very much again.

[FreemanZ · Accepted solution]

hi @AviramWeiss 

Aha, the Date column of the slicer shall come from an independent Date Table with this code:

DateTable = CALENDAR(MIN(TableName[Date]), MAX(TableName[Date]))

And change the code for the measures to:

StartValue =

VAR MinDate = MIN (DateTable[Date])

RETURN

CALCULATE(

    SUM(TableName[Value]),

    TableName[Date] = MinDate

)

EndValue =

VAR MaxDate = MAX (DateTable[Date])

RETURN

CALCULATE(

    SUM(TableName[Value]),

    TableName[Date] = MaxDate

)

Total =

VAR MinDate = MIN (DateTable[Date])

VAR MaxDate = MAX (DateTable[Date])

RETURN

CALCULATE(

    SUM(TableName[Value]),

    TableName[Date] >= MinDate

         &&TableName[Date] <= MaxDate

)

i tried and it now worked like this:

[AviramWeiss]

Works perfectly now. Thank you very much.