Forum Discussion
year to date in query editor
Hello
Is it possible make a year to date column in query editor?
I need the year to date column for a conditional column where would do something like that: if customer turnover is bigger than 10'000 give back the name of the customer, otherwise "Other customers"..
Regards
Matt
There are multiple ways to do it. Here are some links:
https://www.powerquery.training/portfolio/time-intelligence-with-power-query/
https://www.mrexcel.com/forum/power-bi/973390-calculate-ytd-values-power-query.html
https://www.excelguru.ca/blog/2015/03/31/create-running-totals-in-power-query/
https://www.youtube.com/watch?v=ZCxI12JB_ps
if you run into performance-problems, you might need to dig into this thread: https://social.technet.microsoft.com/Forums/en-US/1275f33f-71df-41ee-914f-c482d2f0678e/sumifs-in-power-query-rolling-12-months?forum=powerquery
10 Replies
HasanResolver I
Can you show me the sample data so i can take a look at it?
- ImkeF
Community Champion
There are multiple ways to do it. Here are some links:
https://www.powerquery.training/portfolio/time-intelligence-with-power-query/
https://www.mrexcel.com/forum/power-bi/973390-calculate-ytd-values-power-query.html
https://www.excelguru.ca/blog/2015/03/31/create-running-totals-in-power-query/
https://www.youtube.com/watch?v=ZCxI12JB_ps
if you run into performance-problems, you might need to dig into this thread: https://social.technet.microsoft.com/Forums/en-US/1275f33f-71df-41ee-914f-c482d2f0678e/sumifs-in-power-query-rolling-12-months?forum=powerquery
I am working with approximately 60,000 rows, have tried all of the recommended methods, and they are all quite slow. In Excel, it is simply a calculated column: =IF(M2<M1,F2,N1+F2) with column M the index, column N the running total, and column F the data to be totaled. Of course, that runs in milliseconds (It would be nice to have the ability to code an excel formula in a Power Query column that actually acted like a formula).
The fastest appears to be your solution at excelguru.ca, using recursion - but I have one issue that I can't solve - that being that I need to have a running total based on an index that I created (from 1) via a group by, so it resets to 0 when the index resets.
This was your code from that site:
Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
ChangedType = Table.Buffer(Table.TransformColumnTypes(Source,{{"Date", type date}})),
Iterate = List.Buffer(List.Generate(
()=>[Counter=0, Value_=ChangedType[Sale]{0}],
each [Counter]<=Table.RowCount(ChangedType),
each [Counter=[Counter]+1,
Value_=[Value_]+ChangedType[Sale]{[Counter]+1}],
each [Value_])),
Table = Table.FromColumns({ChangedType[Date], ChangedType[Sale], Iterate}),
Rename = Table.RenameColumns(Table,{{"Column1", "Date"}, {"Column2", "Sale"}, {"Column3", "CumSale"}}),
RemError = Table.RemoveRowsWithErrors(Rename, {"CumSale"})
in
RemErrorAny recommendation on how would I reset the running total on an index reset?
I've created the following columns in each of the calendar table and it all starts with either July or January. But, I'd like to have to start with April of each year and ends with the March of the following year. How it's possible to create a calendar for the financial year April to March 2018?
If you do have any advance editor script, please share it up with me. Somehow, I've managed to find out a couple of things in fact, that didn't satisfy my hunger. It is because whenever I'd try to figure out the visualization the fiscal month numbers and fiscal quarters are not matching up. I've search out a dozens of websites and still they're not able to provide me with a proper solution. Can you please, help me up to figure it out?
- ImkeF
I'm not sure if I got your request right, so please share sample data of:
1) Your source data and
2) The desired result.
Thanks.
If its just generating fiscal calendar, then, open query editor, on the left hand side go to new source, blank query and write below formula
= List.Dates(#date(2017,4,1) , 365 ,#duration(1,0,0,0))
I hope it helps
