Forum Discussion
Power Query: Custom column to sum revenues for remaining months based on selected month number
I am working in Power Query and need to create a custom column to support a simple forecast calculation.
I have monthly revenue data with a month number column (1–12). Based on a selected month number, I want to calculate the sum of revenues for the remaining months of the year.
Example requirement:
- If the selected month number is 5, the custom column should return the sum of revenues from months 6 through 12 (i.e. the next 7 months).
- The calculation should adapt dynamically depending on the selected month.
Hello Abourard,
Try this M code in Power Query. It filters months greater than your selected month and sums their revenues. You can either use a parameter for one forecast point or calculate it row‑by‑row:
1. Using a parameter (single forecast point)
let Source = YourTable, SelectedMonth = 5, // or create a parameter RemainingMonths = Table.SelectRows(Source, each [MonthNumber] > SelectedMonth), SumRemainingRevenue = List.Sum(RemainingMonths[Revenue]), Result = Table.AddColumn(Source, "RemainingRevenue", each SumRemainingRevenue) in Result2. Row‑level calculation (each month forecasts the rest of the year)
Result = Table.AddColumn(Source, "RemainingRevenue", each List.Sum( Table.SelectRows(Source, (r) => r[MonthNumber] > [MonthNumber])[Revenue] ) )Use the parameter approach if you want one forecast based on a chosen month, or the row‑level approach if you want every month to show its own “remaining months” total.
8 Replies
- GeraldGEmerick
Memorable Member
Abourard Are you positive that you want this in Power Query? Because selecting something and having the calculation change dynamically sounds a lot like a measure and that is DAX. If this is Power Query, how is the user selecting the month, in a parameter or is it whatever month is in the current row?
Hi GeraldGEmerick the peried number will be selected from a slicer. Can i add a dax formula a power pivottable?
- GeraldGEmerick
Boubou78 OK, if it is a slicer, you would need to use a DAX measure. It would look something like the following:
Monthly Revenue = VAR _Date = SELECTEDVALUE( 'Table'[Date] ) VAR _Month = MONTH( _Date ) VAR _Year = YEAR( _Date ) VAR _Return = CALCULATE( 'Table'[Value], 'Table'[Year] = _Year, MONTH( 'Table'[Date] ) > _Month ) RETURN _Return
- cengizhanarslan
Super User
You do not reaaly to make the calculation in Power Query, DAX measure can be used to be dynamically used in your visuals. Please check the formula below for your usage:
Remaining Months Revenue := VAR SelMonth = MAX ( 'Date'[MonthNumber] ) VAR SelYear = MAX ( 'Date'[MonthNumber] ) RETURN CALCULATE ( [Revenue], FILTER ( ALL ( 'Date'[MonthNumber] ), 'Date'[MonthNumber] > SelMonth && 'Date'[Year] = SelYear ) ) - Royel
Hi Abourard lets try this
Remaining Months Revenue = VAR SelectedMonth = SELECTEDVALUE('Calendar'[MonthNumber], 0) VAR SelectedYear = SELECTEDVALUE('Calendar'[Year], YEAR(TODAY())) RETURN IF( SelectedMonth > 0, CALCULATE( SUM('Revenue'[Amount]), FILTER( ALL('Calendar'), 'Calendar'[Year] = SelectedYear && 'Calendar'[MonthNumber] > SelectedMonth ) ), BLANK() )Here, we get the selected month and year, then sum all revenue where the month number is greater than what's selected, so picking month 5 gives you months 6 through 12.
If nothing's selected, returning blank instead of potentially misleading numbers.Thanks
- Olufemi7
Hello Abourard,
Try this M code in Power Query. It filters months greater than your selected month and sums their revenues. You can either use a parameter for one forecast point or calculate it row‑by‑row:
1. Using a parameter (single forecast point)
let Source = YourTable, SelectedMonth = 5, // or create a parameter RemainingMonths = Table.SelectRows(Source, each [MonthNumber] > SelectedMonth), SumRemainingRevenue = List.Sum(RemainingMonths[Revenue]), Result = Table.AddColumn(Source, "RemainingRevenue", each SumRemainingRevenue) in Result2. Row‑level calculation (each month forecasts the rest of the year)
Result = Table.AddColumn(Source, "RemainingRevenue", each List.Sum( Table.SelectRows(Source, (r) => r[MonthNumber] > [MonthNumber])[Revenue] ) )Use the parameter approach if you want one forecast based on a chosen month, or the row‑level approach if you want every month to show its own “remaining months” total.
v-kpoloju-msftCommunity Support
Hi Abourard,
Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to Olufemi7, Royel, cengizhanarslan, GeraldGEmerick, for those inputs on this thread.Has your issue been resolved? If the response provided by the community member Olufemi7, Royel, cengizhanarslan, GeraldGEmerick, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.- v-kpoloju-msft
Hi Abourard,
Just wanted to follow up. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.Thank you.
