Percent Change non sequential dates

[MarkDengler]

I am trying to calculate (measure) on the percentage change from Filing Data over Filing date. The issue I am having is the dates are not logical, D/D or M/M to use the Prior Date functions.  When I do try I get the AUM but not specific to the Firm Name I am filtered on (over 1k firms). Ideally, I also need "if no prior date use same FirmAUM" so the % Change isn't skewed.

FilingDatePriorAUM =

CALCULATE(

[Firm AUM],

FILTER(

ALL('ADV FirmInfo')

, 'ADV FirmInfo'[FilingDt].[Date]

=MAX('ADV FirmInfo'[FilingDt])-1))  >>> not working 😞

[lc_finance]

Hi @MarkDengler ,

you can find my proposed solution here.

Here is a screenshot:

And here is the formula for the AUM prior filing date:

AUM Prior filing date = 

VAR currentFirmNumber = [Firm Number]
VAR currentFirmName = [Firm name]
VAR currentFilingDate = [Filing date]

VAR priorDateTable = FILTER(
    'FirmInfo',
    AND('FirmInfo'[Firm Number]=currentFirmNumber, AND('FirmInfo'[Firm name]=currentFirmName, 'FirmInfo'[Filing date]<currentFilingDate))
)

VAR priorDateExist = NOT COUNTX(priorDateTable, [Filing date]) = BLANK()

VAR priorDate = MAXX(priorDateTable,[Filing date])

VAR priorDateAUM = LOOKUPVALUE(FirmInfo[Firm AUM],FirmInfo[Filing date],priorDate,FirmInfo[Firm name],currentFirmName)

RETURN IF(priorDateExist, priorDateAUM, [Firm AUM])

At first, I create a table 'priorDateTable' which includes all the dates except the current date.

Then, I check if a prior date exist (a prior date exist if the table 'priorDateTable' has at least one row)

Following that, I find the prior date 'priorDate', which is the maximum date in the table 'priorDateTable'.

Finally, I use a LOOKUPVALUE to find the AUM corresponding to the prior date.

The percentage change formula is a lot easier;

Percentage change = [Firm AUM]/[AUM Prior filing date]-1

I hope this helps you and do not hesitate if you have any questions!

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[Nathaniel_C · Accepted solution]

Hi @MarkDengler ,
Please try this:

AUM Prior Filing Date =
VAR _curFDate = table[Filing Date]
VAR _calc =
    CALCULATE (
        MAX ( table[Firm AUM] ),
        table[Filing Date] < _curFDate,
        ALLEXCEPT ( table, table[FirmCRDNum] )
    )
VAR _final =
    IF ( ISBLANK ( _calc ), MAX[Firm AUM], _calc )
RETURN
    _final

Edited at 9:42
Let me know if you have any questions. MOCKUP PBIX Mockup 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[MarkDengler]

You are a amazing; this worked beautifully.  Thank you so much! 

[Nathaniel_C]

@MarkDengler ,

You are welcome, thanks for your kind words!

Nathaniel