cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Monthly Sum

Working towards building a formula, but I believe I need to do this in pieces so I'm not confusing anyone. I've asked this a few times and still haven't found my answer... So I will start simple:

1.PNG

 

How can I get the monthly sum for the latest month in this table? (71.28) I'm trying to set it up to where as new monthly revenue comes in, it is getting the total for the latest month and multiplying that figure by 12.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@Anonymous

 

Try putting the *12 after the last parenthesis.

 

Regards

 

Victor

Lima - Peru




Lima - Peru

View solution in original post

7 REPLIES 7
Highlighted
Community Champion
Community Champion

@Anonymous Hi, you can use this measure:

 

 

LastMonthRevenue =
VAR LastMonthRevenueDate =
    MONTH ( LASTDATE ( Table1[Revenue Date] ) )
VAR LastYearRevenueDate =
    YEAR ( LASTDATE ( Table1[Revenue Date] ) )
RETURN
    CALCULATE (
        SUM ( Table1[Revenue] );
        FILTER (
            Table1;
            MONTH ( Table1[Revenue Date] ) = LastMonthRevenueDate
                && YEAR ( Table1[Revenue Date] ) = LastYearRevenueDate
        )
    )

Regards

 

Victor

Lima - Peru




Lima - Peru
Highlighted
Anonymous
Not applicable

@Vvelarde I get this when building the formula:

1.PNG

Highlighted

@Anonymous

 

Try putting the *12 after the last parenthesis.

 

Regards

 

Victor

Lima - Peru




Lima - Peru

View solution in original post

Highlighted
Anonymous
Not applicable

@Vvelarde This is the second time you've literally saved my life. Thank you so much! Works like a charm.

Highlighted
Anonymous
Not applicable

@Vvelarde I do have a follow up question if you don't mind. So my supervisors are trying to decide whether they want to see the latest set of records or the 2nd to last set of records. So if I wanted to set it up based on the 2nd to last set of data, how could I modify the given formula to go back an additional month from the latest month?

Highlighted

@Anonymous

 

Let's made a modification to the measure to be more simple

 

 

LastMonthRevenue_Reloaded =
VAR LastMonthRevenueDate =
    EOMONTH ( LASTDATE ( Table1[Revenue Date] ); 0 )
RETURN
    CALCULATE (
        SUM ( Table1[Revenue] );
        FILTER ( Table1; EOMONTH ( Table1[Revenue Date]; 0 ) = LastMonthRevenueDate )
    )

And -1 month:

 

LastMonthRevenue_Reloaded-1 =
VAR LastMonthRevenueDate_1 =
    EOMONTH ( LASTDATE ( Table1[Revenue Date] ); -1 )
RETURN
    CALCULATE (
        SUM ( Table1[Revenue] );
        FILTER ( Table1; EOMONTH ( Table1[Revenue Date]; 0 ) = LastMonthRevenueDate_1 )
    )

Regards

Victor

Lima - Peru

 




Lima - Peru
Highlighted
Anonymous
Not applicable

@Vvelarde These work perfectly. I really can't thank you enough! Thought I would never figure it out. Thanks again.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors