Converting YTD figures to monthly figures in PowerQuery

[cho]

Hi Experts,

I have a sample table like below:

Account	Company	Type	Period	YTD
001	AAA	B1	202211	100
002	AAA	C1	202211	200
001	BBB	C2	202211	300
007	FFF	B1	202212	200
009	RRR	C1	202212	400
011	AAA	C2	202212	500
007	RRR	B1	202301	200
012	DDD	C1	202301	300
008	BBB	C2	202301	500

But these values are YTD values and I need to convert this to a MTD table like this:

Account	Company	Type	Period	MTD
001	AAA	B1	202201	100
002	AAA	C1	202201	200
001	BBB	C2	202201	300
007	FFF	B1	202202	100
009	RRR	C1	202202	200
011	AAA	C2	202202	200
007	RRR	B1	202301	200
012	DDD	C1	202301	300
008	BBB	C2	202301	500

Can someone help?

[meiwah · Accepted solution]

Hi Cho, unfortunately, my data source is not SQL base. I've found a solution using Power Query. 

1) Add a custom column for YearnMonth

2) Add a custom column for Month

3) Add a custom column for Sales YTD Prev, which is the Sales YTD of the previous month. Using 

try #"Sorted Rows"[Sales YTD] {[Month] - 2} otherwise 0

4) Compute the difference betw [Sales YTD] and [Sales YTD Prev]

I can't upload the source pbix file. Pls contact me if anyone needs it

[HAP]

Hi Meiwah. Thanks for your solution. I'd like to ask to share the PBIX file if possible.

I have YTD value of multiple categories for each month. My fiscal year is from April to March. Is this solution can work for multiple categories ?

[meiwah]

Hi HAP,

can pm me your email? I can't attach the file here 🙂

[meiwah]

Yes, can anyone pls help. I've the same problem. Thanks!

[cho]

Hey @meiwah! I found a solution for this. 

I could not do this in Power Query and I feel there is no point of adding columns there.

What I did was, when loading data, I wrote a small SQL so from data source itself, I convert YTD figures to monthly values.
I used SQL functions like LAG(), OVER(), PARTITION BY().

Another way is to add a monthly column in data source.