Showing results for 
Search instead for 
Did you mean: 
Regular Visitor

Previous month value reset at change of year



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.


I added a new measure:


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


And changed the previous month revenue measure:


Revenue Previous Month = IF([Month1]>0,0,CALCULATE([Revenue],DATEADD('Calendar'[Date],-1,MONTH)))


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 🙂


Thank you for your time 


Community Support
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] )
    IF (
        MONTH ( currDate ) > 1,
        CALCULATE ( [Revenue], DATEADD ( 'Calendar'[Date], -1, MONTH ) ),

Xiaoxin Sheng

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

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 🙂

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.



Xiaoxin Sheng

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

Helpful resources

Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors