Moving Ending NAV based on changing calendar date

[Nagesh20]

Hi all,

Please someone help on the below issue

Issue: "Ending NAV is derived based on the maximum date from the running total (cumulative daily NAV since inception) . Here locking ending NAV at max date. This ending NAV is not moving when changing the max date to earlier calendar date in the slicer. I am trying to get ending NAV move based on changing max date. Provided all the details below.

The iterated DAX formula in calculated column is

Ending NAV =

var currentdate=Fund[Date]

var Currentfun=Fund[Fund]

var Currentuser=Fund[User]

var filter_table=FILTER(Fund,Fund[Date]<=currentdate  

&& Fund[Fund]=Currentfun && Fund[User]=Currentuser) -- Filtr for Running Table

var runtotal =CALCULATE(SUM(Fund[Daily NAV]),filter_table) -- Running table

var _maxdate = CALCULATE(MAX(Fund[Date]),ALLEXCEPT(Fund,Fund[Fund],Fund[User])) -- Locking NAV from running table for Ending date

var filter_table2=FILTER(Fund,Fund[Date]=currentdate

&& Fund[Fund]=Currentfun && Fund[User]=Currentuser)

return

IF(Fund[Date]=_maxdate,runtotal,0)

Before changing the max date (12/31/2024) in slicer

After changing max date to earlier i.e. 12/31/2023 in slicer (Ending NAV should be shows as 652 on 9/5/2023)

Date	User	Fund	Call	Distribution	Daily NAV
3/5/2024	First	B	100	0	100
4/5/2024	First	B	50	0	50
5/5/2024	First	B	25	0	26
6/5/2024	First	B	500	0	502
7/5/2024	First	B	0	100	-100
9/5/2024	First	B	75	0	75
10/5/2024	First	B	0	250	-250
4/5/2023	Third	A	1000	0	1000
5/5/2023	Third	A	45	0	45
6/5/2023	Third	A	0	25	-25
7/5/2023	Third	A	26	0	36
8/5/2023	Third	A	35	45	-10
9/5/2023	Third	A	100	0	100
10/5/2024	Third	A	0	0	26
4/5/2022	Second	B	500	0	500
5/5/2023	Second	B	25	0	25
6/5/2022	Second	B	0	25	-25
7/5/2022	Second	B	26	0	36
7/6/2022	Second	B	0	0	0
8/5/2022	Second	B	35	45	-10
9/5/2022	Second	B	100	0	100
9/5/2023	Second	B	0	0	26
9/5/2024	Second	B	0	0	0

[Nagesh20]

Hi @v-yangliu-msft 

Thanks for the prompt response.

I have tried with your suggested formula, but the total is getting zero. I have not idea how to get this correct.

Alternative way - i have calculated running total in calculated column. Is there any chance to get the DAX measure for ending NAV based on max date from the calculated column.

Running_Total =

var currentdate=Fund[Date]

var Currentfun=Fund[Fund]

var Currentuser=Fund[User]

var filter_table=FILTER(Fund,Fund[Date]<=currentdate  

&& Fund[Fund]=Currentfun && Fund[User]=Currentuser) -- Filtr for Running Table

Return

CALCULATE(SUM(Fund[Daily NAV]),filter_table) -- Running table

[v-yangliu-msft · Accepted solution]

Hi  @Nagesh20 ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _slicerdate=MAXX(ALLSELECTED('Date'),'Date'[Date])
var _maxdate=
MAXX(FILTER(ALL('Fund'),'Fund'[Fund]=MAX('Fund'[Fund])&&'Fund'[Date]<=_slicerdate&&'Fund'[User]=MAX('Fund'[User])),[Date])
var _runtotal=
SUMX(FILTER(ALLSELECTED('Fund'),'Fund'[Date]<=MAX('Fund'[Date])&&'Fund'[Fund]=MAX('Fund'[Fund])&&'Fund'[User]=MAX('Fund'[User])),[Daily NAV])
return
IF(
    MAX('Fund'[Date])=_maxdate,_runtotal,0)

2. Result:

Best Regards,

Liu Yang

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