Forum Discussion
Previous Version Value Dynamic
If you change the data type of version flag to integer this is a piece of cake.
Use the below formula as a calculated column.Prev =var maxindex = maxx(FILTER('Table',EARLIER('Table'[Versionflag])>'Table'[Versionflag]),'Table'[Versionflag])
return CALCULATE(SELECTEDVALUE('Table)'[ForecastVersion]),all('Table'),'Table'[Versionflag]=maxindex)
Proud to be a datanaut,
If the above code works please mark it as a solution if not please reply with what is the problem and I will look into it again.Thanks and regards,Atma.If that is not working try the code sample below.
Prev = var maxindex= maxx(FILTER('Sales forecast',EARLIER('Sales forecast'[Version Flag])>'Sales forecast'[Version Flag]),'Sales forecast'[Version Flag]) return CALCULATE(SELECTEDVALUE('Sales forecast'[Forecast Version]),all('Sales forecast'),'Sales forecast'[Version Flag]=maxindex)Hi AlanP514
Here is the sample file with the solutionhttps://www.dropbox.com/t/1KmBvYEHyXMYUYB6
https://www.dropbox.com/t/Ufe2M7Z5O0hsZdpl
This is PQ solution
= 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 ( 1, Int64.From(Text.BetweenDelimiters([Forcast Version], "V", "-")) - 1, 01,0,0,0 )) & "-" & Number.ToText( Int64.From(Text.AfterDelimiter([Forcast Version], "-", 1))))This is with DAX
Previous Version DAX = VAR CurrentVersion = Data[Forcast Version] VAR CurrentMonth = IFERROR ( MID ( CurrentVersion, 2, FIND ( "-", CurrentVersion, 1 ) - 2 ), BLANK ( ) ) RETURN IF ( NOT ISBLANK ( CurrentMonth ), VAR MonthNumber = IF ( CurrentMonth - 1 = 0, 12, CurrentMonth - 1 ) VAR MonthName = FORMAT ( DATE ( 1, MonthNumber, 1 ), "MMMM" ) VAR CurrentYear = VALUE ( RIGHT ( CurrentVersion, 4 ) ) VAR YearNumber = IF ( CurrentMonth - 1 = 0, CurrentYear - 1, CurrentYear ) RETURN "V" & MonthNumber & "-" & MonthName & "-" & YearNumber )
AlanP514 Good day,
Do we have an index column that helps us to identify the previous version, If the answer is yes this can be achieved.
Regards,
Atma.
Hai Dhacd
yes I had created a version flag
This is a dynamic version flag that I created based on the forecast version column 21 is denoting the year and delimiter 0 and 5 is the version same for 2106
If you change the data type of version flag to integer this is a piece of cake.
Use the below formula as a calculated column.Prev =var maxindex = maxx(FILTER('Table',EARLIER('Table'[Versionflag])>'Table'[Versionflag]),'Table'[Versionflag])
return CALCULATE(SELECTEDVALUE('Table)'[ForecastVersion]),all('Table'),'Table'[Versionflag]=maxindex)
Proud to be a datanaut,
If the above code works please mark it as a solution if not please reply with what is the problem and I will look into it again.Thanks and regards,Atma.