Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Euge4826
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:

powerbi_1.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Desired Table:

excel_1.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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:

 

powerbi_2.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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 

 

3 REPLIES 3
v-shex-msft
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] )
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.

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.

 

Regards,

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

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.