Search code examples
daxpowerbi-desktop

How to find the max date for an individual Mutual Fund Sheme in DAX

Trying to solve a problem in DAX.

Have 4 tables in the model.

  1. All_MF - This table contains the entries of the investment I have done in different schemes over a time period.
  2. ALL_NAV - This table contains the entries of the NAV by date for the different schemes I have invested in. So there would be an entry for every day which has the NAV valeue.
  3. MF_Details - This contains the list of the Schemes where I have invested in.
  4. Date - The standard date table.

The model is as below: Data Model:

Sample files attached: All_MF https://pastebin.com/embed_js/xwXUXhWv?theme=dark

All_NAV https://pastebin.com/embed_js/0EcLuH32?theme=dark

MF_Details https://pastebin.com/embed_js/HiecJepj?theme=dark

I am trying to add a calculated column in the ALL_MF table which would have the latest NAV_DATE for that scheme.

I tried using the below DAX but it populates with the MAX date irrespective of the scheme as I am unable to make a relationship between ALL_MF and ALL_NAV

LatestNAVDt = CALCULATE(MAXX(RELATEDTABLE(ALL_NAV),ALL_NAV[navDate]),ALL(ALL_NAV))

I wrote the below DAX measure to find the NAV value for the max date for the year selected and I am able to get the correct value, but then how do I calculate the current value of the investment as per the latest NAV

NAVValue =
    var MaxNavDt = MAXX(RELATEDTABLE(ALL_NAV),ALL_NAV[navDate])
    Return
        CALCULATE(MAXX(RELATEDTABLE(ALL_NAV),ALL_NAV[navValue]),
        FILTER(ALL_NAV,ALL_NAV[navDate] = MaxNavDt))

Please note that for Axis Midcap Fund - Direct Plan - Growth the NAV data is till 10/19 but for the other two it is till 10/20

Do let me know if I need to clarify as I have invested a lot of time to explain the scenario in detail.

Solution 
  • LatestNAVDt =
  var scheme = 'ALL_MF'[Scheme_Name]
  var result = CALCULATE(LASTDATE('ALL_NAV'[navDate]), FILTER(ALL('ALL_NAV'), 'ALL_NAV'[SchemeName] = scheme))
  RETURN result

    Or via REMOVEFILTERS:

    LatestNAVDt = CALCULATE(LASTDATE('ALL_NAV'[navDate]), REMOVEFILTERS('Date'[Date]) )

    Example: enter image description here Note that I deliberatly changed the data so that Midcap goes upto 10/18 only.