Earlier function not working well

[yishenhui]

Hi,

I was trying to use Earlier function to get a list of current column value but seems not working well. The DAX is

PreviousOccuranceOfWords1 =

 COUNTX(FILTER(MainQuery,

              [Start of Month]=MAXX(FILTER('MainQuery',

                                           'MainQuery'[Start of Month] < EARLIER('MainQuery'[Start of Month])  ),

                                    [Start of Month])),

        MainQuery[Value])

There supposes to show the value of count of previous month, but it doesn't. If I change "<" to "<=", then it shows, but it's current month's count. Not sure where is the error.

[Greg_Deckler]

@yishenhui See my article on Mean Time Between Failure (MTBF) which uses EARLIER. You might need to separate out your calculation of the previous date in a separate VAR. Tends to be easier to troubleshoot. You can also use a VAR to grab your current date (Start of Month) using MAX and then skip using EARLIER and just use that VAR instead.

http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[yishenhui]

Hi Greg,

I had read your article before and wrote the previous DAX. Thank you.

I have separated calculation with step by step. However, there is an error that it couldn't find the column but actually it does exist and it worked when it was in the nested calculation. You may found it in the commented rows below