Forum Discussion

yishenhui's avatar
yishenhui
Helper II
3 years ago

Earlier function not working well

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.
need help in DAX

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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

    • yishenhui's avatar
      yishenhui
      Helper II
      3 years ago

      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