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...
Need Help
meenukannusamy's avatar
meenukannusamy
Regular Visitor
8 years ago

I

I have to caluculate dynamic 12 months rolling total.

  • 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 .

       

      • meenukannusamy's avatar
        meenukannusamy
        Regular Visitor
        8 years ago