Forum Discussion

Nagesh20's avatar
Nagesh20
Icon for Helper I rankHelper I
2 years ago
Solved

Moving Ending NAV based on changing calendar date

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

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • Nagesh20's avatar
    Nagesh20
    Icon for Helper I rankHelper I
    2 years ago

    Hi Anonymous 

     

    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