Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
Solved! Go to Solution.
@Anonymous
Try putting the *12 after the last parenthesis.
Regards
Victor
Lima - Peru
@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
@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?
@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
@Vvelarde These work perfectly. I really can't thank you enough! Thought I would never figure it out. Thanks again.
@Anonymous
Try putting the *12 after the last parenthesis.
Regards
Victor
Lima - Peru
@Vvelarde This is the second time you've literally saved my life. Thank you so much! Works like a charm.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |