Min/Max of Group for Date Range

[Wendeley-North]

Hi, would like some help - hope I've provided enough information.

Desired Outcome

Based on dates selected in slicer:

1. Retrieve past 2 months of data
2. Aggregate [ID] based on [Group], taking the SUM of [Values] in the  [Group] -- This returns an aggregate value, per date
3. For the selected date range, return the minimum for that [Group]

Sample Data

SlicerGroup1	Date	SlicerGroup2	ID	Value	Group	Sub_Group	Sub_Sub_Group	Sub_Sub_Sub_Group
SG1_1	31-Mar-21	SG2_1	ID_1	-0.03%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_1	31-Mar-21	SG2_1	ID_2	0.02%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_1	31-Mar-21	SG2_1	ID_3	-0.04%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_1	30-Apr-21	SG2_1	ID_3	0.02%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_1	30-Apr-21	SG2_1	ID_1	-0.06%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_1	30-Apr-21	SG2_1	ID_2	0.04%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_2	31-Mar-21	SG2_1	ID_4	0.06%	Group_2	G2_SG1	G2_SG1_SSG1	G2_SG1_SSG1_SSSG1
SG1_1	4-May-21	SG2_1	ID_2	0.04%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_1	3-May-21	SG2_1	ID_2	0.02%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_1	4-May-21	SG2_1	ID_1	0.07%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_1	3-May-21	SG2_1	ID_1	-0.05%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_1	4-May-21	SG2_1	ID_3	-0.02%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_1	3-May-21	SG2_1	ID_3	0.03%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_2	30-Apr-21	SG2_1	ID_4	0.02%	Group_2	G2_SG1	G2_SG1_SSG1	G2_SG1_SSG1_SSSG1
SG1_2	31-Mar-21	SG2_1	ID_1	0.04%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_2	31-Mar-21	SG2_1	ID_3	0.04%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_2	31-Mar-21	SG2_1	ID_2	0.00%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_2	30-Apr-21	SG2_1	ID_3	0.04%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_2	30-Apr-21	SG2_1	ID_1	0.08%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_2	30-Apr-21	SG2_1	ID_2	-0.06%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_2	3-May-21	SG2_1	ID_2	-0.07%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_2	4-May-21	SG2_1	ID_2	0.04%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_2	4-May-21	SG2_1	ID_1	-0.07%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_2	3-May-21	SG2_1	ID_1	0.01%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_2	4-May-21	SG2_1	ID_3	0.01%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_2	3-May-21	SG2_1	ID_3	0.02%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_2	4-May-21	SG2_1	ID_4	0.03%	Group_2	G2_SG1	G2_SG1_SSG1	G2_SG1_SSG1_SSSG1
SG1_2	3-May-21	SG2_1	ID_4	-0.02%	Group_2	G2_SG1	G2_SG1_SSG1	G2_SG1_SSG1_SSSG1

Current Output

The current output of interest is shown in the left table of the following image. The right table shows an example of the aggregated values, so if the [Date] is selected as 30-Apr-21, it should be taking in values -0.10% and 0.01%, and returning the minimum, i.e. -0.10% in this case. Based on the measure [2_month_min_datestring], it seems to suggest that the appropriate filters have been applied. Codes for measures are pasted below as well.

2_month_min_datestring = 
VAR endDate = LASTDATE(Table2[Date])
VAR startDate = DATE( YEAR(endDate) - 1, MONTH(endDate), DAY(endDate) )
VAR filteredTable = 
    FILTER (
        ALLEXCEPT ( Table2, Table2[SlicerGroup1], Table2[Group] ),
        Table2[Date] >= startDate &&
        Table2[Date] <= endDate
    )
RETURN
    "FROM " & MINX( filteredTable, Table2[Date] ) & " TO " & MAXX ( filteredTable, Table2[Date] )

2_month_min_value = 
VAR endDate = LASTDATE(Table2[Date])
VAR startDate = DATE( YEAR(endDate) - 1, MONTH(endDate), DAY(endDate) )
VAR filteredTable = 
    FILTER (
        ALLEXCEPT ( Table2, Table2[SlicerGroup1], Table2[Group] ),
        Table2[Date] >= startDate &&
        Table2[Date] <= endDate
    )
RETURN
    MINX ( filteredTable, SUM( Table2[Value] ) )

[v-yingjl · Accepted solution]

Hi @Wendeley-North ,

To get the min value in the date range, not need to use sum() to aggreate them.

2_month_min_value = 
VAR endDate = LASTDATE(Table2[Date])
VAR startDate = DATE( YEAR(endDate) - 1, MONTH(endDate), DAY(endDate) )
VAR filteredTable = 
    FILTER (
        ALLEXCEPT ( Table2, Table2[SlicerGroup1], Table2[Group] ),
        Table2[Date] >= startDate &&
        Table2[Date] <= endDate
    )
RETURN
    MINX ( filteredTable, [Value] )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Wendeley-North]

Bump - still looking for help. 

[amitchandak]

@Wendeley-North ,Join table with a date table and then try  or with filter on date of you atble

a measure like this should filter automatically  for range 

calculate(sum(Table[Value]))

last 2 months

2_month_min_datestring =
Var _max = minx(allselected(Date), Date[Date])
Var _max = eomonth(_max,-2)+1
return
calculate(sum(Table[Value]), filter(all('Date'),Date[Date] >=_min && Date[Date]<= _max))

measure across

calculate(sum(Table[Value]),
ALLEXCEPT ( Table2, Table2[SlicerGroup1], Table2[Group] ) )

or

2_month_min_datestring =
Var _max = minx(allselected(Date), Date[Date])
Var _max = eomonth(_max,-2)+1
return
calculate(sum(Table[Value]),
ALLEXCEPT ( Table2, Table2[SlicerGroup1], Table2[Group] ) , filter(all('Date'),Date[Date] >=_min && Date[Date]<= _max))

Min of group

minx(values([Group]), calculate(sum(Table[Value])))

[Wendeley-North]

Hi, I tried to create a 'Date' table as instructed:

Then I used the following code:

2_month_min_test = 
Var _max = MAXX(ALLSELECTED('Date'), 'Date'[Date])
Var _min = EOMONTH(_max,-2)+1
var _intermediate =
    CALCULATE(  SUM (Table2[Value] ),
                ALLEXCEPT ( Table2, Table2[SlicerGroup1], Table2[Group] ) , 
                            FILTER( ALL('Date'), 'Date'[Date] >=_min && 'Date'[Date]<= _max ) 
                )
RETURN
    MINX(   VALUES ( Table2[Group] ), 
            CALCULATE ( SUM ( Table2[Value] ))
            ) 

Unfortunately, it seems to give the same (wrong) result as my measure. Any idea why? Thanks.

The bottom table is using your measure: