Obtaining Monthly Totals from YTD Data

[stephanie116]

I receive a monthly report that shows YTD totals for a few different categories. I'm trying to find a solution within Power Query to convert these YTD totals into monthly totals. Any ideas?

Raw Data: Monthly Reports

JANUARY REPORT

Category A	50
Category B	100
Category X	0

FEBRUARY REPORT

Category A	125
Category B	197
Category X	15

MARCH REPORT

Category A	185
Category B	251
Category X	39

Current Dataset: 

MONTH	CATEGORY	YTD TOTAL
January	Category A	50
January	Category B	100
January	Category X	0
February	Category A	125
February	Category B	197
February	Category X	15
March	Category A	185
March	Category B	251
March	Category X	39

Target Dataset: 

MONTH	CATEGORY	MONTHLY TOTAL
January	Category A	50
January	Category B	100
January	Category X	0
February	Category A	75
February	Category B	97
February	Category X	15
March	Category A	60
March	Category B	54
March	Category X	24

[edhans · Accepted solution]

@stephanie116 see if this works. Here is what I did - as requested - in Power Query

1. I created an ad-hoc date table to give me dates, then added the names of the months and month numbers to that table.
2. In your table, I merged with my date table by the Month Name column. Then I expanded the Month number and sorted by month. Now I can ensure the data is correctly sorted for the next steps. It looks like this:
   1. 
3. Then I added an Index column
4. Then I added the table back into itself as a nested table by simply referring to the previous step. So now every record of the table has a nested version of itself. Note: I don't know how this will perform on very large record sets, but your data doesn't seem like it will be in the millions of records.
   1. 
5. Next I filter that nested table to only include items in the current category where the index is less than or equal to the current index.
   1. 
   2. It is assigining to varIndex and varCategory the index number and category of the main table, then it does a filter (Table.SelectRows) on the nested table. The way I've assigned variables is similar to how variables work in DAX, or the EARLIER() function in DAX if you know how that works.
6. Finally I add a List.Sum to total the [Total] column still showing in my now filtered table.
7. Get rid of all of the unnecessary columns.

End result:

You can see my PBIX file here if you want to play with it.

EDIT: I improved it a bit by only nesting the necessary data and doing the total calculation (List.Sum()) in a single setp. The step immediately after adding the index is now:

= Table.AddColumn(#"Added Index", "Cumulative YTD Total", 
each let varCategory = [Category], varIndex = [Index]
in
List.Sum(
     Table.SelectRows(#"Added Index", each [Category] = varCategory and [Index] <= varIndex)[Total]
), Int64.Type)

For a smaller data set (under 100,000 records probably, maybe more) this should work fine. I strongly encourage you to read @ImkeF's links and study them. I plan to dig into those later today and see if I can improve this even more for my own use going forward.

The original longer query is still in the PBIX linked to called [Table (Original Long Way)] to follow along, but [Table] consolidates a number of steps as shown above and pulls in only the necessary data for each record vs all the data for every record and then filtering in subsequent steps.

[ImkeF]

Hi there,

I come across a question like this before and made a blogpost about the solution: https://www.thebiccountant.com/2018/08/31/unravel-cumulative-totals-to-their-initial-elements-in-pow... 

@edhans solution has a very nice and transparent logical path, but the performance could be improved if the logic would be applied on category-level, like I've described here: 

https://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-power... 

[amitchandak]

@stephanie116 

You need to assign month a number like 1,2, 3 4 or month year 202001, 2020002, etc. Assume that column is Month No. Then try a new column

MONTHLY TOTAL = table[YTD TOTAL] -maxx(filter(Table,table[Month No] <= earlier(table[Month No]) && table[CATEGORY] = earlier(table[CATEGORY])),table[YTD TOTAL])

[Greg_Deckler]

I don't know about Power Query, but I did write a solution in DAX:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Revenue-Reverse-YTD/m-p/373185#M111

@ImkeF might know how to solve it in M.