Return minimum/maximum of historical date range - almost working code.

[Wendeley-North]

UPDATE: 26 May - Unfortunately still unable to get it working. I've taken the time to create dummy data so it reflects the actual code as much as possible.

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] ) )

Appreciate any guidance, many thanks - I've been spending many hours on this, but my initial measures still got me the closest.

--- 

ORIGINAL MESSAGE:

Hi, I've a visualization that currently takes the end date from a slicer on the page. I would like the measure to:

1. Look back at the previous 12 months
2. Aggregate the quantity by [Group1] for each of the month -- Context provided by rows
3. Return the maximum/minimum of these aggregates for [Group1].

I currently have a measure that feels like it is almost there:

1Y_min = 
VAR endDate = LASTDATE(Table[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate) - 1, MONTH(endDate), DAY(endDate) ) -- 1-year lookback
VAR filteredTable = 
    FILTER (
        ALLEXCEPT ( Table, Table[ParentGroup], Table[Group1] ), -- Create a new table, ignoring the Date slicer filter
        Table[Date] >= startDate &&
        Table[Date] <= endDate
    )
VAR sumQuantity =
    SUM ( Table[Quantity] )
RETURN
    MINX ( filteredTable, sumQuantity ) 

I do know for a fact that the filtered table is returning the correct date ranges by looking at the results returned by the following code:

1Y_min = 
VAR endDate = LASTDATE(Table[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate) - 1, MONTH(endDate), DAY(endDate) ) -- 1-year lookback
VAR filteredTable = 
    FILTER (
        ALLEXCEPT ( Table, Table[ParentGroup], Table[Group1] ),
        Table[Date] >= startDate &&
        Table[Date] <= endDate
    )
RETURN
    "FROM " & MINX( filteredTable, Table[Date] ) & " TO " & MAXX ( filteredTable, Table[Date] ) 

However it seems to fall apart the moment I try to get the MIN/MAX ranges - it is clearly only returning the SUM for the date specified in the slicer (since MIN/MAX returns the same results).

Would appreciate any help, thanks.

[Wendeley-North]

Hi, I'm trying to make use of the bullet charts add-on, and would like to create a few measures, detailed below.

To start with, some sample data:

Date	Grouping 1	Item ID	Sector	FilterGroup	Quantity
29-Jan-99	A	12431232	ZZ	X	-100
26-Feb-99	A	12312333	ZC	Y	10
26-Feb-99	A	12334123	ZA	X	-10
26-Feb-99	A	12342332	ZB	X	-20
31-Mar-99	A	12321231	ZA	X	50
29-Jan-99	B	16453453	ZA	Y	100
26-Feb-99	B	13232322	ZF	X	50

Existing slicer contexts:

1. Date
2. Grouping 1

What the measures should do:

1. Measure 1 - Dynamic axis setting by finding the minimum of all quantities, and add an buffer:
   1. Summarize the table by grouping using the column "sector" to sum "quantity"
      • Filter for "FilterGroup = X", i.e. all rows which have FilterGroup <> "X" are not considered.
   2. Return the minimum amongst all the sectors (for the dates returned by the slicer)
2. Measure 2 - Same as measure 1 but for maximum

Here's what I've tried (but doesn't work at all, unfortunately - also the rounding a little strange compared to the whole numbers presented in the sample table above, but the actual data involves smaller numbers, hence the way it is handled):

BulletChart_Min = 
VAR group_act_wgt =
    SUMMARIZE(  FILTER (    ALLSELECTED( Table ),
                            Table[FilterGroup] = "X" ),
                Table[Sector],
                "Sector Average Quantity",
                AVERAGE( Table[Quantity] )
    )
VAR minimum =
    MINX ( group_act_wgt, [Sector Average Quantity] )
RETURN
    MROUND( minimum, -0.005 ) - 0.001

Separately, I would like to, for the same data, obtain a measure that allows me retrieve the minimum historical data, specific to the sector, thus the measure should:

1. Summarize the table by grouping using the column "sector" and "date" to sum "quantity"
   • Filter for "FilterGroup = X", i.e. all rows which have FilterGroup <> "X" are not considered.
   • I'm guessing since it is historical, the ALL function may have to be used since slicer filters are in play.
2. Return the minimum for each sector - say, over a period of the past 1-year

Same for the maximum (which I should be able to figure one once some guidance on how to create the minimum measure is provided).

Have been tearing my head out over this, would appreciate any guidance.

Many thanks.

[Wendeley-North]

Bump - still attempting to work out a solution.

[daxer-almighty]

2 things. First,

Summarize the table by grouping using the column "sector" to sum "quantity"

Summing is not averaging and in your measure you're trying to average. Second, you should never use SUMMARIZE to calculate anything inside it. SUMMARIZE should only be used for grouping and nothing else. Instead, you should use the combination SUMMARIZE/ADDCOLUMNS, where SUMMARIZE groups and ADDCOLUMNS calculates. This is the only safe way to do what you're trying to achieve with just SUMMARIZE (it's a long story why this is so but you can find articles about it on www.sqlbi.com).

Here's a measure that might do what you're after:

[Wendeley-North]

Hi @daxer-almighty ,

Thanks - your solution worked like a charm. Regarding the use of "average" in my code attempt - yes that was my mistake, I was trying out various expressions for the sake of it.

Regarding my second query of finding the historical min/max for a certain lookback, do you think you can help with that as well? Given what you've already provided I will look into amending the query further to try to get what I need, and will update accordingly if I figure it out before you reply - albiet unlikely.

[daxer-almighty]

This

Return the minimum for each sector - say, over a period of the past 1-year

does not make too much sense or... you could phrase it more clearly. A measure can't return a table, only a scalar, so it's not possible to return a value for each sector if many sectors are selected. The values must be aggregated somehow.

[Wendeley-North]

Hi, 

Thanks for being willing to help, much appreciated. I'll try again.

The measure will be returning a scalar, and this scalar will return the minimum/maximum of the total quantity recorded for, as an example, the past year.

The visualization (bullet chart) provides the row context (sector), for the grouping.

So what the measure does is:

1. Take into account the slicer filters:
   • Grouping 1
   • Date (e.g. 18 May 2021)
2. With the above context, the measure then:
   1. Filters for Grouping 1
   2. Filters for dates of the [Quantity] from 18 May 2020 to 18 May 2021 
   3. Groups the resulting table by Sector and Date, and sums the quantity
      • At this point, the quantities are calculated for a sector (context provided by the visualization), for each of the dates returned from the filter.
   4. The measure then takes MIN ( SUM ( Table[Quantity] ) ) with the groupings applied
      • This returns the minimum aggregated quantity, for a sector, over the past 12 months, which is necessarily a scalar value.

I would also need to retain the KEEPFILTERS(Table[FilterGroup]="X") presented in your original solution.

Do let me know if there is any ambiguity I can elaborate on, if any.

[Vera_33]

Hi @Wendeley-North 

Not sure if I understand it correctly, you want to find the min value of SUM ( Table[Quantity] ) on Month level in past 12 month? Do you have day level data and a Month column? It is better to post some sample data...and your visual to display it with Table[ParentGroup], Table[Group1]?

1Y_min = 
VAR endDate = LASTDATE(Table[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate) - 1, MONTH(endDate), DAY(endDate) ) -- 1-year lookback
VAR filteredTable = 
    FILTER (
        ALLEXCEPT ( Table, Table[ParentGroup], Table[Group1] ), -- Create a new table, ignoring the Date slicer filter
        Table[Date] >= startDate &&
        Table[Date] <= endDate
    )
VAR t1 =
    GROUPBY(filteredTable, [Month],"sumQuantity",SUMX (CURRENTGROUP, [Quantity] ))
RETURN
    MINX ( t1, [sumQuantity] ) 

[Wendeley-North]

Hi @Vera_33,

Yes that is correct. It is currently just based on a date column, could I then use [Date].Month to do the grouping instead?

Here is some sample data:

Date	ParentGroup	Item ID	Group1	FilterGroup	Quantity
29-Jan-99	A	12431232	ZZ	X	-100
26-Feb-99	A	12312333	ZC	Y	10
26-Feb-99	A	12334123	ZA	X	-10
26-Feb-99	A	12342332	ZB	X	-20
31-Mar-99	A	12321231	ZA	X	50
29-Jan-99	B	16453453	ZA	Y	100
26-Feb-99	B	13232322	ZF	X	50

Will try out your solution in a bit and get back to you, cheers. 

As for the visual, Table[ParentGroup] is a slicer selection, so the visual will just have Table[Group1] as row context.

[Vera_33]

Hi @Wendeley-North 

I don't think [Date].Month will work...I don't use this kind of auto date/time in Power BI...

[Wendeley-North]

Hi Vera, your measure didn't work, unfortunately.. I've updated the main post to include more details and a better set of sample data, hope it makes it easier for you. Thanks.

[Vera_33]

Hi @Wendeley-North 

I used your updated dummy data, but I got different values, do you consider Sub Group as well? It should be -0.05% if no Sub Group, not -0.01% in your post. Am I missing something? It's better you can share a .pbix file, will pm you shortly.

[Wendeley-North]

Hi @Vera_33 ,

That's strange, since my dummy data is exactly what I've posted above. Even if I account for subgroups:

It seems like none of them should be returning -0.04%?

Unfortunately I'm unable to upload anything due to firewall restrictions, but I've tried my best to replicate from scratch what I'm showing here to hopefully make it easier to troubleshoot..

[Wendeley-North]

@Vera_33 I retried your solution, but there seems to be some error:

2m_min_vera = 
VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate) - 1, MONTH(endDate), DAY(endDate) ) -- 1-year lookback
VAR filteredTable = 
    FILTER (
        ALLEXCEPT ( Table2, Table2[SlicerGroup1], Table2[Group] ), -- Create a new table, ignoring the Date slicer filter
        Table2[Date] >= startDate &&
        Table2[Date] <= endDate
    )
VAR t1 =
    GROUPBY(filteredTable, 'Date'[Month],"sumQuantity",SUMX (CURRENTGROUP, [Value] ))
RETURN
    MINX ( t1, [sumQuantity] ) 

EDIT:

I used CURRENTGROUP() instead of CURRENTGROUP and it seems to work! I'll try it on the actual data and report back in a couple of minutes:

2m_min_vera = 
VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate) - 1, MONTH(endDate), DAY(endDate) ) -- 1-year lookback
VAR filteredTable = 
    FILTER (
        ALLEXCEPT ( Table2, Table2[SlicerGroup1], Table2[Group] ), -- Create a new table, ignoring the Date slicer filter
        Table2[Date] >= startDate &&
        Table2[Date] <= endDate
    )
VAR t1 =
    GROUPBY(filteredTable, 'Date'[Month], "sumQuantity", SUMX (CURRENTGROUP() , [Value] ))
RETURN
    MINX ( t1, [sumQuantity] ) 

[Wendeley-North]

@Vera_33 

The measure seems to only return the correct value when 30-Apr-21 is chosen. When any other dates are chosen, it seems like it returns the same value of -0.10%, when it should be -0.15% when 3-May-21 or 4-May-21 is chosen, for example.

[Vera_33]

Hi @Wendeley-North 

I feel like I need to understand the value you want to return first...let's take 30-Apr-21, you need to sum up value per each month and find the Min. 

It's -0.05% (-0.04%,0.02%,-0.03%), or -0.04% taking Sub_Group into account, otherwise I misunderstood the whole thing.

[Wendeley-North · Accepted solution]

Hi @Vera_33 ,

Looking at the sample data I provided again it seems like it may be different from the one I'm using - apologies for that. I was using the RAND() generator in excel and must've accidentally refreshed it. In any case, I managed to build on what you provided and finally got it working. Here's the following code:

2m_min_vera = 
VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate), MONTH(endDate)-2, DAY(endDate) ) -- 2-month lookback
VAR slicerSelection = VALUES( Table2[SlicerGroup1] ) 
VAR filteredTable = 
    FILTER (
        ALLEXCEPT( Table2, Table2[Group] ), -- Create a new table, keeping the Group row context
        Table2[SlicerGroup1] IN slicerSelection &&
        Table2[Date] >= startDate &&
        Table2[Date] <= endDate
    )
VAR t1 =
    SUMMARIZE(filteredTable, Table2[Date], "sumQuantity", SUM ( [Value] ))
RETURN
    -- CONCATENATEX( t1, Table2[Group], "," ) Used for debugging
    -- CONCATENATEX( t1, FORMAT([sumQuantity], "0.00%"), "," ) Used for debugging
    FORMAT( MINX ( t1, [sumQuantity]), "0.00%" )

It seems like using ALLEXCEPT( Table2, Table2[SlicerGroup1]) was somehow not taking into account the slicer selection, since the debugging formulas indicated that slicergroup1 SG1_2 was being included in the filtered table as well.

I also swapped out the GROUPBY with SUMMARIZE mid-way, though they seem to serve the same function.

Thank you so much.

[Wendeley-North]

Thanks for the tip, I tried the [Date].month and indeed it didn't work, I'll try creating a separate date table and get back to you shortly. Cheers.