12 Month Total by Client based on Report Date

[rmalhan12]

I have the table above which lists the monthly income by month by client. I wanted to insert another column which would add the 12 month total income per client based on the report date and the client ID.

I have tried to use the formula below but to no success as it gives me the total value for all clients on each line.

12MonthRI =

VAR _MAX = EOMONTH(IncomeSummary[ReportDate],0)

VAR _MIN = EOMONTH(IncomeSummary[ReportDate],-12)+1

return

SUMX(FILTER(IncomeSummary,IncomeSummary[ClientId] && IncomeSummary[ReportDate] <= _MAX && IncomeSummary[ReportDate] >= _MIN),IncomeSummary[GrossFCI])

Any help is appreciated or even a different way of getting the correct figure. 

[amitchandak · Accepted solution]

@rmalhan12 , for new column try like

12MonthRI =
VAR _MAX = EOMONTH(IncomeSummary[ReportDate],0)
VAR _MIN = EOMONTH(IncomeSummary[ReportDate],-12)+1
return
SUMX(FILTER(IncomeSummary,IncomeSummary[ClientId] =earlier(IncomeSummary[ClientId] ) && IncomeSummary[ReportDate] <= _MAX && IncomeSummary[ReportDate] >= _MIN),IncomeSummary[GrossFCI])

For measures

try like

examples

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

Try new DAX function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

Rolling Months Formula: https://youtu.be/GS5O4G81fww