Forum Discussion

Anonymous

Not applicable

3 years ago

Solved

Converting YTD figures to monthly figures in PowerQuery

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
3 years ago
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

5 Replies

meiwah
Resolver I
3 years ago
Yes, can anyone pls help. I've the same problem. Thanks!
- Anonymous
  Not applicable
  3 years ago
  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.
meiwah
Resolver I
3 years ago
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
  Regular Visitor
  2 years ago
  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
    Resolver I
    2 years ago
    Hi HAP,
    
    can pm me your email? I can't attach the file here 🙂

Related content