Hi All @ankit was fantastic a getting me through the first hurdle on this one.
Unfortunately though I have a few other issues:
The measure only works when the dates are in context. So Dec 19 to Feb 20, gives the correct result (in Test Matrix). But when we use a Jan 20 to Feb 20 date range the result should be the same but it isn’t. I have changed the date context to look at ALL(Dates), but to no avail. I guess the question here is how to remove context from a CALCULATEDTABLE. But googling didn’t help.
The matrix (Bsheet Amts (Avg 3m Measure)) does not seem to be calculating at all. It should read the same as test Matrix in Feb 2020. the other months being a rolling 3 m average.
@BINavPete Please be reminded that while we hope for everyone’s participation in the forum, we don’t encourage single calling out of a member to answer a question as this may discourage other users to help your inquiry.
While waiting for other members to jump in, I suggest using the forum search to discover if your query has been asked before by another member. Thanks!
Checked the new measure. It has a field named maxOffset. This will change based on Date selection as it will always consider the last month selected in Date Slicer. Hence, whenever the date changes the value in Test Matrix will change. If need to always take the maxoffset i.e. of last month in calendar irrespective of Date slicer, can use below
Var maxOffset = CALCULATE(max(‘Calendar’[AccountingPeriodOffset]),all(‘Calendar’))
For calculating 3M Avg , can try below formula. This will again be driven by Offset based on Fiscal Month Year in the Columns of Matrix.
3M New = Var thisoffset = -3
//Date filter for YTD/Balance Sheet Average
Var maxOffset = CALCULATE(max('Calendar'[AccountingPeriodOffset]),'Calendar'[Date]=max('Calendar'[Date]))
var MinDate = CALCULATE(min('Calendar'[Date]),all('Calendar'),'Calendar'[AccountingPeriodOffset] = maxOffset + thisoffset + 1)
var MaxDate = CALCULATE(MAX('Calendar'[Date]),all('Calendar'),'Calendar'[AccountingPeriodOffset] = maxOffset)
Var DateFilterYTD = CALCULATETABLE(MAFact,MAFact[PostingDate] >= MinDate && MAFact[PostingDate] <= MaxDate,all('Calendar'))
Var AllDates = All('Calendar'[Date])
//Date filter for PTD/Profit and loss averages
Var DateFilterPTD = FILTER(All('Calendar'),'Calendar'[Date] >= MinDate && 'Calendar'[Date] <= MaxDate)
Var basetable = ADDCOLUMNS(SUMMARIZE(DateFilterYTD,
Reports[RepKey],
Reports[repLinedesc],
'Calendar'[Fisc Month Year],
Companies[BusinessUnit],"startdate",max('Calendar'[Fisc YearStart]),"EndDate",max('Calendar'[Date])),"Last3 YTD",CALCULATE([LineAmount],Streams[Stream] = "Actual",filter(all('Calendar'),'Calendar'[Date] >= [startdate] && 'Calendar'[Date] <= [EndDate])))
Return
SWITCH(TRUE(),
Max(Depts[DeptName])="Balance Sheet",
If(HASONEVALUE(Reports[RepKey]),
calculate(DIVIDE(SUMX(basetable,[Last3 YTD]),thisoffset * -1,blank()),All('Calendar'[Date])),
0
)
)