topic Re: Earlier function not working well in Report Server

Earlier function not working well

yishenhui — Mon, 07 Aug 2023 20:45:05 GMT

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.

Re: Earlier function not working well

Greg_Deckler — Mon, 07 Aug 2023 20:50:50 GMT

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/339586.
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

Re: Earlier function not working well

yishenhui — Tue, 08 Aug 2023 14:06:51 GMT

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