Rolling Fiscal YTD Average

[dogt1225]

Hi All, 

I am looking to create a line and column chart on Power BI. The Column chart will be the Average Days to Resolve (ADR) by Fiscal Week and the Line chart will be the Rolling Average Fiscal YTD.

These are my calculations so far. I need help with calculation the rolling average Fiscal YTD by Fiscal Week. I have a Calendar[Date] table that holds my Fiscal Year and Fiscal Week calculations. Thank you!

[Cumulative Sum of Days to Resolve] = 
CALCULATE(SUMX(DataTable, DATEDIFF(DataTable[Submitted_Date], DataTable[Date Resolved], DAY)),

USERELATIONSHIP(DataTable[Date Resolved], 'Calendar'[Date])

)

[Date Resolved (Count)] = 

IF(

CALCULATE(

COUNT(DataTable[Date Resolved]),

USERELATIONSHIP(DataTable[Date Resolved], 'Calendar'[Date]),

DataTable[Date Resolved] <> BLANK(),

DataTable[Submission Type] <> BLANK(),

DataTable[Request Status] = "Resolved"

) = BLANK(),

0,

CALCULATE(

COUNT(DataTable[Date Resolved]),

USERELATIONSHIP(DataTable[Date Resolved], 'Calendar'[Date]),

DataTable[Date Resolved] <> BLANK(),

DataTable[Submission Type] <> BLANK(),

DataTable[Request Status] = "Resolved"

)

)

[ADR] = 

[Cumulative Sum of Days to Resolve] / [Date Resolved (Count)]

 

[v-jayw-msft]

Hi @dogt1225 ,

The picture was broken, can you share some sample data?

Best Regards,
Jay

[amitchandak]

@dogt1225 , not clear , you can use end date in dates ytd and get it

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"5/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"5/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"5/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"5/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"5/31"))

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

[dogt1225]

@amitchandak Im not quite getting the correct answer. I tried the following calculation and this is the result vs the correct result. 
The FY are from Feb -> Jan

[ADR Rolling v2] = Calculate( [ADR], DATESYTD('Calendar' [Date], "1/31")

[DataZoe]

@dogt1225 

Rolling Weekly Average 12 months =
VAR LastDate_ =
    LASTDATE ( Calendar[Date] )
RETURN
    CALCULATE (
        AVERAGEX ( VALUES ( Calendar[Fiscal Week] ), CALCULATE ( [ADR] ) ),
        FILTER (
            ALL ( Calendar ),
            [Date] <= LastDate_
                && [Date] > DATEADD ( LastDate_, -12, MONTH )
        )
    )

which is modified from https://community.powerbi.com/t5/DAX-Commands-and-Tips/Rolling-average-last-3-months/m-p/938544#M990... 

[dogt1225]

@DataZoe  Unfortunately this did not work. I am still getting zeros as the Rolling ADR.