Values based on max date of slicer

[EFGPFS]

I am trying to get the value for the max date based on the slicer. Below is an explanation of what I am trying to do:

Here is my starting data:

Fund	Date	Value
1	12/21/2021	100
1	3/15/2022	150
1	4/16/2022	50
2	11/14/2021	200
2	1/16/2022	150
2	2/16/2022	200
3	10/8/2021	300
3	1/15/2021	50
3	3/18/2022	150

I want the data to show only the values for the max date selected by slicer and the rest of the values to be 0.

So if I choose a slicer with a max date of 3/31/2022, the following would be the output:

Fund	Date	Value
1	12/21/2021	0
1	3/15/2022	150
1	4/16/2022	0
2	11/14/2021	0
2	1/16/2022	0
2	2/16/2022	200
3	10/8/2021	0
3	1/15/2021	0
3	3/18/2022	150

Or if I choose a slicer with a max date of 2/18/2022, the following would be the output:

Fund	Date	Value
1	12/21/2021	100
1	3/15/2022	0
1	4/16/2022	0
2	11/14/2021	0
2	1/16/2022	0
2	2/16/2022	200
3	10/8/2021	0
3	1/15/2021	50
3	3/18/2022	0

I have tried the following function but it always chooses the max date of the entire data set and not the slicer.

Terminal Value Date = IF('Data'[Date]=MAXX(allselected('Data'),'Data'[Date]),'Data'[Value],0)

I have tried searching onlinethe past couple of days and I can't seem to find a solution that works. Any advise will be much appreciated. Thanks!

[CNENFRNL]

For fun only, a showcase of powerful Excel worksheet formula; MAXIFS() is better if available in your Excel,

[EFGPFS]

Thanks! What is the Total in your measure?

[EFGPFS]

@CNENFRNL 

Sorry I didn't see the download at first. I tried your method but get the following error for value retained:

"All arguments within an ALLEXCEPT function must be related to (or contained by) the table which is used as the first argument. Where a one-to-many relationship exists, the table which is used as the first argument must be on the 'many' side of that relationship."

The "Value" I am using in my example is a calculated column of a running total, and seems to throw an error in the solution you suggested. See below.

Value = 
    CALCULATE(
        SUM('Data'[Transaction Amount]),
        FILTER(
            'Data',
            'Data'[Fund]=EARLIER('Data'[Fund])
            && 'Data'[Date] <= EARLIER('Data'[Date])
            )
            )

So my data looks like this:

Fund	Date	Transaction Amount	Value
1	12/21/2021	100	100
1	3/15/2022	50	150
1	4/16/2022	(100)	50
2	11/14/2021	200	200
2	1/16/2022	(50)	150
2	2/16/2022	50	200
3	1/15/2021	50	50
3	10/8/2021	250	300
3	3/18/2022	(100)	150

Do you have any suggestions for how to deal with the error? Any help is greatly appreaciated. Thanks!

[CNENFRNL · Accepted solution]

I don't need a calculated column at all,

[EFGPFS]

@CNENFRNL Thanks, I really apperciate your help. This solution kind of works, however, I am ultimatly trying to calculate an XIRR of the Accum field plus a cashflow field. 

So if I choose a data of 2/18/2022, the data would appear as the folowing:

Fund	Date	Cashflow	Transaction Amount	Accum	Cashflow + Accum
1	12/21/2021	(10.00)	100	100	90.00
1	3/15/2022	(30.00)	50	0	0
1	4/16/2022	(40.00)	-100	0	0
2	11/14/2021	(50.00)	200	0	(50.00)
2	1/16/2022	10.00	-50	0	10.00
2	2/16/2022	(20.00)	50	200	180.00
3	1/15/2021	(30.00)	50	0	(30.00)
3	10/8/2021	(60.00)	250	300	240.00
3	3/18/2022	20.00	-100	0	0

I am able to get a table that looks like the above, however, the XIRR of the Cashflow + Accum is not calculating correctly.

To calculate Cashflow + Accum I use the following measure:

Cashflow + Accum = sum('Data'[Cashflow])+_Measures[Accum]

Then I use the following measure to calculate the XIRR:

XIRR = 
VAR Summary =
    SUMMARIZE ( 'Data', 'Data'[Date], "@Amt", _Measures[Cashflow + Accum])
VAR FilterZeros =
    FILTER ( Summary, [@Amt] <> 0 )
RETURN
    XIRR ( FilterZeros, [@Amt], 'Data'[Date] )

I think the XIRR isn't calculating correctly beacuse the Cashflow + Accum is a measure and not calculated column. Do you have any suggestions on how to get the XIRR to work?  Note the data is made up and the XIRR may not converge in the example I have given.

Any help would be greatly appreaciated.

[SpartaBI]

@EFGPFS I created an example with your data:
MaxDate.pbix

Showcase Report – Contoso By SpartaBI

      

[EFGPFS]

@SpartaBI  Thanks, that isn't quite what I am looking for but I really appreciate your effort. For each fund there should only be one value based on the max date and the rest would be 0. Your solution shows all values less than the max date. I only want the value for the most max date less than the slicer date. In the tables below I highlighted the differences.

So for example if I choose 2/18/2022 in your workbook I get the following results:

Fund	Date	Value
1	12/21/2021	100
1	3/15/2022	0
1	4/16/2022	0
2	11/14/2021	200
2	1/16/2022	150
2	2/16/2022	200
3	10/8/2021	300
3	1/15/2021	50
3	3/18/2022	0

When what I am looking for is the following results when I set the slicer to 2/18/2022:

Fund	Date	Value
1	12/21/2021	100
1	3/15/2022	0
1	4/16/2022	0
2	11/14/2021	0
2	1/16/2022	0
2	2/16/2022	200
3	10/8/2021	0
3	1/15/2021	50
3	3/18/2022	0

Please respond if you have any more thoughts on the problem. Again I appreciate any help!!

[SpartaBI]

@EFGPFS change the measure in my file to:

Measure Result = 
VAR _current_date = MAX('Table'[Date])
VAR _max_selected_date = MAX('Calendar'[Date])
VAR _max_date_per_fund_before_max_selected_data = 
    CALCULATE(
        MAX('Table'[Date]), 
        REMOVEFILTERS('Table'),
        VALUES('Table'[Fund]),
        'Table'[Date] < _max_selected_date 
        )
VAR _result = 
    IF(_current_date <= _max_date_per_fund_before_max_selected_data,  SUM('Table'[Value]), 0)
RETURN
    _result

Showcase Report – Contoso By SpartaBI