cancel
Showing results for
Did you mean:
Regular Visitor

## Previous month value reset at change of year

Hi,

I have tried to search for the answer I need but I'm struggling. Our company posts financial journal entries each month. Every time they post an entry they do it as the total year to date. I need to show the monthly value.

I have created the following measures:

1. Revenue = SUM('Values'[Revenue])
2. Revenue Previous Month = CALCULATE([Revenue],DATEADD('Calendar'[Date],-1,MONTH))
3. Revenue Monthly = [Revenue]-[Revenue Previous Month]

The problem is that I need the previous month revenue to reset to 0 then the year changes. Below is random sample data.

Current PowerBI table:

Desired Table:

In my calendar table I have a month number column so i have tried to add a rule for previous month value that if the month number is 1 then 0 else previous month value.

Month1 = CALCULATE(SUM('Values'[Revenue]),'Calendar'[MonthNum] = 1)

And changed the previous month revenue measure:

But it appears to break the column totals for revenue previous month and revenue monthly total:

I assume because the total row doesnt have a month number so therefore it shows £0 which is also why revenue monthly column is just revenue since it is doing [Revenue]-[Revenue Previous Month].

Any assistance would be greatly appreciated as I really want to be able to produce my desired table 🙂

3 REPLIES 3
Community Support

HI @Euge4826,

In fact, you only need to modify your formula to add condition to check current month to replace value(if month equal to 1):

```Revenue Previous Month =
VAR currDate =
SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
IF (
MONTH ( currDate ) > 1,
CALCULATE ( [Revenue], DATEADD ( 'Calendar'[Date], -1, MONTH ) ),
0
)
```

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Regular Visitor

Thank you for taking the time to reply. Your calculation works great per row but the overall totals are still showing incorrectly. I think it is because the column total isnt the total of all the rows but is running the same formula rule and returning 0.

Jan | Revenue £50 | Revenue Previous Month - £0 | Revenue Monthly - £50

Feb | Revenue £500 | Revenue Previous Month - £50 | Revenue Monthly - £450

Mar | Revenue £1000 | Revenue Previous Month - £500 | Revenue Monthly - £500

Total | Revenue £1150 | Revenue Previous Month - £0 (instead of £550) | Revenue Monthly - £1550 (instead of £1000)

I would appreciate any further suggestions you might have 🙂

Community Support

Hi @Euge4826,

Measure who has specific filters or condition not fully works on total level, it will drill to detail records to calculate and summary result instead direct calculated on summary records.

For this scenario, you need add condition to check total row and write specific formula for total level calculation.

If you are confused on coding formula, please share some sample data with table format and I'll test on them.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.