Year to Date Column (POWER Query)

[Huizar]

Hello Geniuses,

I need some help; I have been researching but cannot find anything to suit my need. I'm looking for a column to provide me a YTD # or Month Text. Since the report i will be running will be refreshed monthly it will help automat some things. This is what I'm looking for exactly:

My data set has January data so i want the column to say Jan YTD or 1. It doesn't matter. Now lets say now I have Jan data and February information in my data set; Now I want the column say to FEB YTD or 2. Then when March data comes in I will have Jan, Feb, and March information in my data set, so I want the column to say Mar YTD or 3.

Hopes this makes sense. Any feedback would be appreciated.

Thanks,

RH

 

[alansidman]

How about showing us some sample data and a mocked up solution. 8-12 records will suffice. Please no pictures as they cannot be manipulated. Use XL2BB. Also, please let us know what version of excel you are using as that may affect the solutions provided.

 

[GraH]

Would your data contain a date field?
Then you can add a step that takes the date columns and from that list return the max date.
Date.Month(List.Max(Source[DateColumn])).

Call that step YTD.

Now add a column in which you refer to that step.
Table.AddColumn(Source, "YTD", each YTD)

If your data does not contain a date field, you still can apply the pattern. However you should use the date field from the creation date or last update date from the source file.