Use cumulative values and point in time values in same visual

[seabrew]

I am trying to compare management fees against an account’s assets under management (AUM). In the data model, the AUM values reflect the market value of an account as of the last day in the month. The fees are what the firm is paid. The fees and AUM are two fields in the same fact table.

I am trying to show quarter-end AUM values with cumulative fees within that quarter. What happens is that the both values are summed up for the quarter. This works for the fees, since they are cumulative. For the AUM, I need to show the value as of the quarter end, not the cumulative AUM over the quarter.

Is this possible in PowerBI / DAX to return the data in this format?

Here’s an example of fact table data and what I need for the report output.

Fact Table
Acct	As Of Date	AUM	Fee
1	3/31/2017	2,000,023.76	120.00
1	2/28/2017	1,078,021.78	110.00
1	1/31/2017	1,000,019.80	100.00
1	12/31/2016	2,000,017.82	90.00
1	11/30/2016	1,230,015.84	80.00
1	10/31/2016	1,100,013.86	70.00
1	9/30/2016	1,090,011.88	60.00
1	8/31/2016	1,100,239.90	50.00
1	7/31/2016	1,050,007.92	40.00
1	6/30/2016	1,600,005.94	30.00
1	5/31/2016	1,500,003.96	20.00
1	4/30/2016	1,000,001.98	10.00

Report Values to Return
Acct	Date	AUM	Fee
1	3/31/2017	2,000,023.76	330.00
1	12/31/2016	2,000,017.82	240.00
1	9/30/2016	1,090,011.88	150.00
1	6/30/2016	1,600,005.94	60.00

[v-chuncz-msft · Accepted solution]

@seabrew,

You could refer to the following steps.

1) add a calculated column to Fact table

End Of Quarter =
ENDOFQUARTER ( 'Fact'[As Of Date] )

2)add a new table

Report = 
GROUPBY (
    'Fact',
    'Fact'[Acct],
    'Fact'[End Of Quarter],
    "Date", MAXX ( CURRENTGROUP (), 'Fact'[As Of Date] ),
    "AUM", MAXX (
        CURRENTGROUP (),
        IF ( 'Fact'[As Of Date] = 'Fact'[End Of Quarter], 'Fact'[AUM] )
    ),
    "Fee", SUMX ( CURRENTGROUP (), 'Fact'[Fee] )
)