Forum Discussion

RichWyeth's avatar
RichWyeth
Frequent Visitor
9 years ago
Solved

Rolling Calculation

Hi,   I have a table as shown below that I would like to run some rolling months from. I am fairly new to Power BI and Dax so struggling a little. Ideally I would like to display a graph that has ...
Need Help
  • RichWyeth's avatar
    RichWyeth
    9 years ago

    Hi,

     

    Unfortunately this website is blocked by our internbet security, so I am unable to view.

     

    However, thanks to Bhavesh Patel, I have managed to understand the process more and seem to have found my solution:

     

    NEWTEST_C = CALCULATE([TotalWorkingDaysActual_M],
              DATESINPERIOD('KPI_ABS'[Month_Year],
                           LASTDATE('KPI_ABS'[Month_Year]),-11,MONTH
             ))

     

    This gives me the result I need each month.

     

    Thank you Bhavesh for your patience and help.

     

    Kind Regards

    Rich.

RichWyeth's avatar
RichWyeth
Frequent Visitor
9 years ago

Hi,

 

I have selected my Date Ranges table and date, but still get errors:

BhaveshPatel's avatar
BhaveshPatel
Super User
9 years ago

Hi There,

 

Please write two measures shown in the screen shot. It is tested and working fine. 

 

 

Thanks & Regards,

Bhavesh

  • RichWyeth's avatar
    RichWyeth
    Frequent Visitor
    9 years ago

    I have written the following two measures:

     

     

    TotalWorkingDaysActual_M = SUM(KPI_ABS[WorkingDaysActual_C])

     

     

    Rolling Average 12 Months_M = CALCULATE (
        [TotalWorkingDaysActual_M],
        DATESBETWEEN(
            DATE_RANGES[Date],
      NEXTDAY( SAMEPERIODLASTYEAR ( LASTDATE ( DATE_RANGES[Date] ))),
            LASTDATE ( DATE_RANGES[Date] )
        )
    )

     

    I have  then created a graph and a time filter using the Date_Ranges[Date] for consistency. But my graph is still showing the over all total for each month.

     

     

    Any test I do by turning measures to columns just show a value of 674 for each month?

     

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

      RichWyeth

       

      A calendar table is necessary if we’d like to use the Time Intelligence Functions. Please take a look at the .pbix file provided by BhaveshPatel. It seems to work well. In case you still have problem with it, please post back.

       

      Best Regards,

      Herbert

      • RichWyeth's avatar
        RichWyeth
        Frequent Visitor
        9 years ago

        Hi,

         

        Unfortunately this website is blocked by our internbet security, so I am unable to view.

         

        However, thanks to Bhavesh Patel, I have managed to understand the process more and seem to have found my solution:

         

        NEWTEST_C = CALCULATE([TotalWorkingDaysActual_M],
                  DATESINPERIOD('KPI_ABS'[Month_Year],
                               LASTDATE('KPI_ABS'[Month_Year]),-11,MONTH
                 ))

         

        This gives me the result I need each month.

         

        Thank you Bhavesh for your patience and help.

         

        Kind Regards

        Rich.

    • rajannaap's avatar
      rajannaap
      Regular Visitor
      7 years ago

      Hi ,

       

      i need simillar help on my data

       

      i have colums like 

      Period        divsion   segment    net

      201501          a              1              12332

      201502          b              2              21233

      ..                    ...             ....               ......

      201812          a              2              312333

       

      I need to calcuate avg  net value (from 201701 to 201712)  for each division and segment by skipping recent 12 periods say (201801 to 201812)

       

      your help would be appreciated.

      Thanks