Dynamic Custom Column

[Anonymous]

Hai All I need your Full support On this 
Scenario : I  Have Sales table and users will send every new version on month basis.
I have a forecast version column like this |

And every month i am appending new version with previous one 
Requirment is i want to create one Custom column for previous version as dynamically
For eg : Now let's Assume latest version is V6-June-2021 and i want to create a dynamic custom column in power query or deskptop

 Out put should be like this 
Latest Version V7-June-2021
Previous Version V6-June-2021
Latest Version V6-june-2021
previous Version V5-May-2021

[tamerj1]

Hi @Anonymous 

Do you mean "July"?

[Anonymous]

Sorry , it's My mistake
V7-July-2021

[tamerj1 · Accepted solution]

@Anonymous 
Sorry for the late reply. I got involved with some other business. Here is the sample file with the solution https://www.dropbox.com/t/dDXhM0NNwthWhfEy

Actually I allways prefer DAX. I find it much more simple than PQ. However, as along as you need this to be part of your source data please use the following M code

= Table.AddColumn(#"Promoted Headers", "Previous Version", 
each if Int64.From(Text.BetweenDelimiters([Forcast Version], "V", "-")) -1 = 0 
then "V12-December-" & Number.ToText( Int64.From(Text.AfterDelimiter([Forcast Version], "-", 1)) - 1 ) 
else  "V" & Number.ToText( Int64.From(Text.BetweenDelimiters([Forcast Version], "V", "-")) - 1 ) 
& "-" & Date.MonthName (#datetime ( 2020, Int64.From(Text.BetweenDelimiters([Forcast Version], "V", "-")) - 1, 01,0,0,0 )) 
& "-" & Number.ToText( Int64.From(Text.AfterDelimiter([Forcast Version], "-", 1)) - 1))

[Anonymous]

Hai @tamerj1 Can you  try to create same solution in dax format, this solution is working 

[amitchandak]

@Anonymous ,

Create a new column in DAX (on in power query)

Date = datevalue(right([version], len([version]-1) )

Now create a rank

Rank  = rankx(Table, [Date],,desc, dense)

now

flag for latest

if([Rank] =1 1, "Latest", [Rank])

these column can be used Rank can also used to find by adding +1

refer examples, I have used -1 as I have desc rank (Custom Period)

DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s