how to get the maximum value within a selection and use that value to filter a computed column

[Roy_B]

I have a view that contains info similar to the table below and to display this data I’m using a matrix.

The columns that are NOT marked with YTD can be summarized normally using the sum function

Columns that Are YTD can either be one of two things based on what date the user selected. if the user selects no dates then the maximum date is used and the totals within the matrix would be  6, 30, 10 for plan YTD, actual YTD and last week YTD. If the user selects 7/25/2020 the total will be 75, 100 30. 

but if the user selects BOTH 7/25 and 8/8 then only the result for the date 8/8 should show 

week#	Product	Plan	Actual	last Week	date	Plan YTD	Actual YTD	last Week YTD
30	A	15	5	1	7/25/2020	20	50	30
30	B	0	1	1	7/25/2020	25	50	0
30	C	15	5	0	7/25/2020	30	0	0
31	A	0	0	2	8/1/2020	10	25	20
31	B	0	0	2	8/1/2020	11	25	0
31	C	10	5	0	8/1/2020	12	0	0
32	A	9	3	0	8/8/2020	1	0	10
32	B	0	3	3	8/8/2020	2	20	0
32	C	0	0	3	8/8/2020	3	10	0

I’m using this formula in one of the computed columns

 
calc_ACTUAL_YTD = IF(Query1[week#] = [Measure], Query1[ActualYTD], 0)

for the Measure I have tried these but none seem to work

Measure = CALCULATE( MAX(Query1[week#]), ALLSELECTED(Query1))
Measure = WEEKNUM(LASTDATE(ALLSELECTED(Query1[Date])))
Measure = MAX(Query1[week#])

But I either get the maximum date value or I get the week value that I need but all values in the [calc_ACTUAL_YTD] column is 0 except for the max date or it still sums everything up

[Anonymous]

HI @Roy_B 

try something like

CALCULATE (
    COUNTROWS('Raw Data'),
    FILTER ( ALLEXCEPT('Raw Data','Raw Data'[Decision]),'Raw Data'[Decision]="Award")
)

or

VAR SearchValue = <Search_Value>
RETURN
    CALCULATE (
        SELECTEDVALUE ( <Result_ColumnName>, <Alternate_Result> ),
        FILTER (
            ALLNOBLANKROW ( <Search_ColumnName> ),
            <Search_ColumnName> == SearchValue     -- The == operator distinguishes between blank and 0/empty string
        ),
        ALL ( <table_of_Result_ColumnName> )       -- If Result_ColumnName is t, this is ALL ( t )
    )

[Roy_B]

Filter value is not known on design time.

What ever fomula I use it must only get the maximum value from the seelcted filters

[v-lionel-msft · Accepted solution]

Hi @Roy_B ,

Like this?

--Before filtering the [date] column

--After filtering the [date] column

__A_YTD = 
VAR x = MAXX( FILTER( ALL(Sheet3), Sheet3[Product] = MAX(Sheet3[Product]) ), [date] )
VAR y = 
CALCULATE(
     SUM([Actual YTD]),
     FILTER(
         ALL(Sheet3),
         Sheet3[date] = x
     )
)
RETURN
IF(
    ISFILTERED(Sheet3[date]),
    CALCULATE(
        SUM(Sheet3[Actual YTD]),
        FILTER(
            Sheet3,
            Sheet3[date] = x
        )
    ),
    y
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.