Forum Discussion
Converting YTD figures to monthly figures in PowerQuery
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
Resolver IYes, can anyone pls help. I've the same problem. Thanks!
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.