Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Running total

Hi All,

 

I want to create a running total (Not YTD) based on previous 12 months. eg; in May 2017 result should be the count of data points from April 2016 and April 2017 count of data points from March 2016.

 

I used following formula, which doesn't sum up the 12 months data.

RN = CALCULATE(COUNT(DATA[Risk Number]),DATESINPERIOD(DATA[Date Occurred],Lastdate(DATA[Date Occurred]),-12,MONTH))

                             

                                    

Appreciate if anyone has a solution for it. 

Data Modeling
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    Anonymous

     

    Please create a relationship between the Calendar and LTI table. Then use YearMonth in the X-Axis of the Line chart as below.

     

     

    Best Regards,
    Herbert

6 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    Anonymous

     

    Please try with following two measures.

     

    Total_1 = 
IF (
    ISBLANK ( SUM ( Table1[Sales] ) ),
    BLANK (),
    CALCULATE (
        SUM( Table1[Sales] ),
        DATESBETWEEN (
            'Calendar'[Date],
            FIRSTDATE ( DATEADD ( 'Calendar'[Date], -11, MONTH ) ),
            LASTDATE ( 'Calendar'[Date] )
        )
    )
)

    Total_2 = 
IF (
    ISBLANK ( SUM ( Table1[Sales] ) ),
    BLANK (),
    CALCULATE (
        SUM( Table1[Sales] ),
        DATESBETWEEN (
            'Calendar'[Date],
            FIRSTDATE ( DATEADD ( 'Calendar'[Date], -12, MONTH ) ),
            ENDOFMONTH ( DATEADD ( 'Calendar'[Date], -1, MONTH ) )
        )
    )
)

     

    Best Regards,
    Herbert

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    Anonymous

     

    Does above solution work on you side?

     

    Best Regards,
    Herbert

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi Herbert v-haibl-msft,

       

      Unfortunately, it doesn't work. It only returns the total of the month not the aggregate value of last 12 months.

      • v-haibl-msft's avatar
        v-haibl-msft
        Microsoft Employee
        9 years ago

        Anonymous

         

        That's strange. It works in my attached .pbix file. Could you please share your .pbix file through online file service like OneDrive? So that I can look into it.

         

        Best Regards,
        Herbert