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...
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

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