Forum Discussion

rmalhan12's avatar
rmalhan12
Frequent Visitor
3 years ago
Solved

12 Month Total by Client based on Report Date

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. 
General Comment
Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    3 years ago

    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

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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