Forum Discussion

meenukannusamy's avatar
meenukannusamy
Regular Visitor
8 years ago

Rolling 12 months data with 2 dimension

Hi All,

I need the sum of values  for prior 12 months .

Example for apr 2017 i need the sum of value from april 2016 to april 2017.help me to acive this in power bi 

HOURS WORKED Jul-16 Aug-16 Sep-16 Oct-16 Nov-16 Dec-16 Jan-17 Feb-17 Mar-17 Apr-17 May-17 Jun-17 Jul-17 Aug-17 Sep-17 Oct-17 Nov-17 Dec-17 Jan-18
Plant  8,643 9,625 8,770 8,805 7,795 7,993 5,462 6,138 7,905 8,270 6,709 8,218 8,548 9,278 8,955 8,489 7,465 7,716 5,935

Need Help

8 Replies

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Hi meenukannusamy,

       

      Please try out the demo here.

      1. Create a calendar table.

      Calendar =
CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2018, 3, 31 ) )

      2. Create a measure.

      Measure =
CALCULATE (
    SUM ( FactTable[Plant] ),
    DATESINPERIOD (
        'Calendar'[Date],
        EOMONTH ( MIN ( 'Calendar'[Date] ), 0 ),
        -12,
        MONTH
    )
)

      According to your snapshots, the period of April 2017 could be from May 2016 to April 2017.

      Rolling_12_months_data

       

      Best Regards,

      Dale

       

      • meenukannusamy's avatar
        meenukannusamy
        Regular Visitor
        8 years ago

        Thanks for your reply sir.But i got another dimension next to date and the calculation has to be repeated for everygroup.In your Example consider there is another column next the year as red and they have data from 2016 to 2017 and blue 2016 to 2017 and we want running total to be calulated across the colour column also. I will be glad if you could help me on that .