Rolling 12 month average

[Anonymous]

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

[Fowmy]

@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]

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.

[Syndicate_Admin]

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]

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

[Greg_Deckler]

@Anonymous First piece of advice, don't use TI functions. 2nd piece of advice:

Rolling Months - Microsoft Power BI Community