Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Rolling average monthly. Prob: Considering previous year[month].

Hi,

 

I'm trying to calculate rolling average, but facing problem when year changes. 

It's considering previous year while calculation.

Finding rolling average of "Cost per pair -USD" and Costper pair usd = [Total Epenses] / [QTY]

 



It is showing .21 (Rolling Avg.) instead of .18 (Year changes, So fresh cal.), higlighted with red line. It is because of considering previous month result 

 

I used Quick measure in power bi for rolling average.

 

Rolling Avg. = IF(
ISFILTERED('Dummy_Table'[ETA]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFMONTH('Dummy_Table'[ETA].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Dummy_Table'[ETA].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)),
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Dummy_Table'),
'Dummy_Table'[ETA].[Year],
'Dummy_Table'[ETA].[QuarterNo],
'Dummy_Table'[ETA].[Quarter],
'Dummy_Table'[ETA].[MonthNo],
'Dummy_Table'[ETA].[Month]
),
__DATE_PERIOD
),
CALCULATE([Cost per Pair], ALL('Dummy_Table'[ETA].[Day]))
)
)

 Expected result.

When I tried in excel it look like. It was just simply with formula. 
Instead of .21 I want .18, as year changes from 2017 to 2018.

 

 

 

7 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support

    Hi dheer1688,

     

    Which DAX formula are you using on excel side? And which one is your expected result?

     

    Regards,

    Jimmy Tao

    • Anonymous's avatar
      Anonymous
      Not applicable

      v-yuta-msft 

      Hi,

       

      I used formula in excel. 

       

      Expected result :

      Instead of .21 for Jan2018 it should show .18 as year changes from 2017 to 2018.

  • Hi,

     

    Assuming the following, try this measure:

     

    1. There is a calendar Table; and
    2. There is a relationship from the Date column of your Dummy_Table to the Date column of the Calendar Table

     

    =CALCULATE([Cost per pair - USD],DATESYTD(Calendar[Date],"31/12"))

     

    If this does not get the correct answer, then share the link from where i can download your PBI file.