How to find the Latest/Max date for the individual Scheme

[alearner]

A newbie looking for some help. 

I am woring on creating a Power BI report for the investments that I do.  Having two main tables. 

1. First table contains investment details for all the transactions that I have done.

2. Second table contains the NAV values for all of the Schemens for which I have done the investments.

3. There is obvously the data table

4. Have this 4th table derived from the 1st table which contains the schemes that  I have invested in.

In ALL_MF I am trying to create a calculated column which should have the latest NAV date for a scheme that is present in the ALL_NAV table.

I have created the below DAX but the issue is that it selects the MAX date and not the MAX date by the Scheme.

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

 

[alearner · Accepted solution]

I was able to achieve what was required using the below DAX

LatestNAVDt =

  var scheme = 'ALL_MF'[Scheme_Name]

  var result = CALCULATE(LASTDATE('ALL_NAV'[navDate]), FILTER(ALL('ALL_NAV'), 'ALL_NAV'[SchemeName] = scheme))

  RETURN result

[alearner]

I was able to achieve what I wanted using the below:

LatestNAVDt =

  var scheme = 'ALL_MF'[Scheme_Name]

  var result = CALCULATE(LASTDATE('ALL_NAV'[navDate]), FILTER(ALL('ALL_NAV'), 'ALL_NAV'[SchemeName] = scheme))

  RETURN result

[alearner]

any help or direction would highly be appreciated ? 

[FreemanZ]

hi @alearner ,

from you model, there is no one-many relationship between ALL_NAV and ALL_MF table, so RELATED/RELATEDTABLE won't work.

try to distill the issue by removing all irrelevant info and post the supporting sample data.

[alearner]

@FreemanZ 

thanks for guiding me through. 

Wanted to update that if I create a measure any try to get the NAV for the latest date selected I get the correct value. The DAX I have used is 

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