Forum Discussion

Rosh89's avatar
Rosh89
Icon for Helper I rankHelper I
3 years ago
Solved

DAX for calculating previous month values

Hi,   I have a table as shown below: Month Period Customer Bonus Previous bonus Jan-22 1 A 100   Feb-22 2 A 200 100 Mar-22 3 A 150 200 Jan-22 1 B 300   Feb-22...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Rosh89 ,

     

    You could add a date column like below:

    date = DATEVALUE("1-"&'Table'[Month])

    Then create a measure:

    Measure = CALCULATE(SUM('Table'[Bonus]),FILTER(ALLEXCEPT('Table','Table'[Customer]),'Table'[date]=EDATE(SELECTEDVALUE('Table'[date]),-1)))

     

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

Rosh89 You need something to define "before", either a Date or an Index and then it's a simple matter. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: 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