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 IHi 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
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 🙂