Formula to return value corresponding to latest date

[niko18033]

Hi Folks - I'd appreciate some help tackling this issue. Basically, I'd like to "fix" a measure based upon the result of a different measure, corresponding to the latest date in the date column (which would be today since this is refreshed daily).

I've tried a myriad of approaches, including lastdate(date), max(date), today(), but none have worked. I was able to fix it on the most recent date (see below), but this is not dynamic and will change when the data is refreshed.

10 week mean = CALCULATE(

        [10 week rolling avg],

        'FA Counts'[CREATEDATE] IN { DATE(2019, 5, 13) } )

Basically I need to use this custom rolling average function, but just for the latest week. I'm using this for a subset of the standard deviation formula (x-mu)^2, where Mu in this example must be 68.1.

I need the standard deviation forumula to read each row's count individually (x) but subtract it from the fixed mean of 68.1, since that is the last 10 wk average. The preceding rolling averages are not relevant.

Date	Count	10 week rolling avg	10 week mean
3/4/2019	60	8.8	68.1
3/11/2019	79	14.8	68.1
3/18/2019	53	22.7	68.1
3/25/2019	66	28	68.1
4/1/2019	80	34.6	68.1
4/8/2019	83	42.6	68.1
4/15/2019	52	50.9	68.1
4/22/2019	68	56.1	68.1
4/29/2019	64	62.9	68.1
5/6/2019	76	65.3	68.1
5/13/2019	77	68.1	68.1

Any help would be much appreciated!

[parry2k]

@niko18033 try this measure

10 Week Mean1 = 
VAR __mostRecentDate = CALCULATE( MAX( Table3[Date] ), ALL( Table3[Date] ) )
RETURN 
CALCULATE( [10 week rolling avg],Table3[Date] = __mostRecentDate )