DAX for calculating previous month values

[Rosh89]

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	2	B	50	300
Mar-22	3	B	200	50

I want to calculate the previous bonus using DAX. I can only create a calendar table with month and not date for this table. Can someone suggest a suitable DAX to get the previous bonus? 

[v-jayw-msft · Accepted solution]

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)))

[bsheffer]

why not create a date in your calendar table using date(yyyy, mmm, 01) as First_of_month for each month and then you can use date functions to get the prior month?

is the month a text string?

[Rosh89]

@bsheffer  In this table month is a text. I tried what you mentioned about using date as first of every month but it does not work that way because you need a calendar table marked as a date table with continous dates without any gap. Without a calendar table containing continous dates the date functions dont work. 

[bsheffer]

if you want to use the calendar table (which doesn't have to be marked as a date table), you can use many available code snippets to create it. 

Then you have to create a column in your fact table that converts your month, year field [Month] into a date (which is why I suggested date(yyyy, mm, 01) where yyyy is like right('fact'[month], 4) and mm is probably a switch statement). 

That date can be linked to the calender table and now you can use all the date functions like adddate() on that table to get your monthly totals for whatever month range you want.

[Shaurya]

Hi @Rosh89,

You can use:

Previous Bonus = SUMX('Table',IF('Table'[Month].[MonthNo]=EARLIER('Table'[Month].[MonthNo])-1 && 'Table'[Customer]=EARLIER('Table'[Customer]),'Table'[Bonus],0))

Works for you? Mark this post as a solution if it does!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI

[Rosh89]

Hi @Shaurya 

The Dax works for this table but it doesnt run on my data model which is around 500K rows. There is no error and it just keeps working. I think it is not able to calculate for so many rows. Perhaps a measure is best for this scenario.

[Shaurya]

Hi @Rosh89,

It keeps working because the EARLIER function compares the expression for every row in the table with all other rows. Give it some time, it should give you the right result.

[Greg_Deckler]

@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/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