Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Rolling 12 month average

Dear all,

I know there are same topics posted, but I'm still confusing. Please help.

I'm trying to write a formula that calculates the average of the rolling 12-month date difference. 

The date difference is calculated as below:

datediff = DATEDIFF('Time to NC priority determination 12m'[NC Date Opened],'Time to NC priority determination 12m'[NC Initial Priority On],DAY)
NC Date Opened and NC Initial Priority On are the only two date columns, ranging from  01/01/2016 to 08/16/2022. I also have a calendar table, and I've set up a relationship. 
Here is my formula for the rolling avg:
Average of datediff rolling average =
CALCULATE(DATEDIFF(VALUES('Time to NC priority determination 12m'[NC Date Opened]),VALUES('Time to NC priority determination 12m'[NC Initial Priority On]),DAY),
DATESINPERIOD('Date Table'[Date],
       LASTDATE('Date Table'[Date]), 12, MONTH))
/
CALCULATE(DISTINCTCOUNT('Date Table'[Date]),
DATESINPERIOD('Date Table'[Date],
       LASTDATE('Date Table'[Date]), 12, MONTH))
This returns nothing, I'm not familiar with using a calendar table in the formula. Please help me.
Thanks in advance
Need Help
Tutorial Requests

5 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    4 years ago

    Total Cumulative Customer Cost Mobile Period =
    Total Customer Cost Accumulated in the period between the last 12 months, including current month (e.g. Current month July, Calculation made since July and August of last year)
    CALCULATE(
    [Total to be calculated],
    DATESINPERIOD(
    Dates[Date], //name of your date table and date column
    LASTDATE(Dates[Date]),
    -12,
    MONTH
    )
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Syndicate_Admin, I have tried the code you mentioned. But I don't know why it returns me blank

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    4 years ago

    Anonymous 

    First, add a calculated column to your table to calculate the Date Difference as follows


    Create a relationship between the Dates[Date] and Time to NC priority determination 12m'[NC Date Opened]


    Create the following measure:

    Avg Rolling 12 Months = 
CALCULATE(
    AVERAGE( 'Time to NC priority determination 12m'[datediff] ),
    DATESINPERIOD( 'Dates'[Date] , MAX( 'Dates'[Date] ) , -12 , MONTH )
)

    Result

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Fowmy, I did what you mentioned above, but it still returns blank.
      I don't know why that happened, my Date Table has a larger range than NC Date Opened and NC Initial Priority On (There are some duplicates in NC Date Opened and NC Initial Priority On). I'm not sure if this is the reason of return blank.