Forum Discussion

Icon for Helper I rank

Helper 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	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?

Anonymous

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

8 Replies

Greg_Deckler
Community 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
Shaurya
Memorable Member
3 years ago
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
  Helper I
  3 years ago
  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
    Memorable Member
    3 years ago
    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.

Anonymous

Not applicable

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
Continued Contributor
3 years ago
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
  Helper I
  3 years ago
  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
    Continued Contributor
    3 years ago
    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.

Related content