Max date in selected week

[mykolasg]

Hello

i have the following data. Final purpose is to select week and get Balance of that weeks max date. Looking forward to suggestions.

Date	Week Number	Vendor	Balance
2022-01-03	1	Amazon	1000
2022-01-04	1	Amazon	95
2022-01-05	1	Amazon	400
2022-01-06	1	Amazon	900
2022-01-07	1	Amazon	1500
2022-01-08	1	Amazon	1999
2022-01-09	1	Amazon	10000
2022-01-10	2	Amazon	1000
2022-01-11	2	Amazon	95
2022-01-12	2	Amazon	400
2022-01-13	2	Amazon	900
2022-01-14	2	Amazon	1500
2022-01-15	2	Amazon	1999
2022-01-16	2	Amazon	10000

[v-rzhou-msft · Accepted solution]

Hi @mykolasg ,

I think you want to calculate the date of max balance in select week. You can try this code.

Max Date select week =
VAR _SELECT_WEEK =
    SELECTEDVALUE ( 'Dim Week Number'[Week Number] )
VAR _MAX_Balance =
    CALCULATE (
        MAX ( 'Table'[Balance] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Week Number] = _SELECT_WEEK )
    )
VAR _MAX_Date =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Week Number] = _SELECT_WEEK
                && 'Table'[Balance] = _MAX_Balance
        )
    )
RETURN
    _MAX_Date

Result is as below.

Best Regards,
Rico Zhou

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

[truptis]

Hi @mykolasg ,

Try this:

Result = CALCULATE(LASTNONBLANKVALUE(Tablename[Date], sum(Tablename[Balance])))

 @mykolasg please mark this as a solution & hit the thumbs up if it helps you. Thanks.

[Jihwan_Kim]

Hi,

I am not sure how your desired outcome visualization looks like, but please check the below picture and the attached pbix file.

Max date balance: = 
LASTNONBLANKVALUE ( Data[Date], SUM ( Data[Balance] ) )

[amitchandak]

@mykolasg , Try like

calculate(lastnonblankvalue(Table[Date], sum(Table[Balance])))

Use allexcept if needed