Forum Discussion
Solved
Previous Version Value Dynamic
Hai All I need help. I am working On a sales report, so every month I am getting a new version of data, so I am using the append option. So I want to create a calculated column or in power ed...
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 )
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)
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.
