Previous month value reset at change of year

[Euge4826]

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.

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 

[v-shex-msft]

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

[Euge4826]

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 🙂

[v-shex-msft]

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