Previous Version Value Dynamic

[AlanP514]

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 editor Calculated column for dynamic previous versions
For eg:  Now I have two versions V5-May-2021 And V6-June-2021 
So I want to create a previous version dynamic column as Dax or POwer query
the output should be like 
when the new version arrives

Forecast Version(Already existing)	Previous version(Want to create)
V6-JUNE-2021	V5-May-2021
V7-July-2021	V6-June-2021
V1-January-2022	V12-December-2021

[Dhacd · Accepted solution]

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.

[Dhacd · Accepted solution]

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)

[tamerj1 · Accepted solution]

Hi @AlanP514 
Here is the sample file with the solution

https://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]

Super Bro , amazing soution

[Dhacd]

@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.

[AlanP514]

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

[Dhacd · Accepted solution]

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.

[AlanP514]

Hai @Dhacd 
Can you check this

[AlanP514]

Hai @Dhacd 
Thanks for the response 
Why this error is happening here, I had followed you same code

[Dhacd]

My bad can you remove that ")" which is highlighted below.

[AlanP514]

Hai @Dhacd 

Again there is a syntax issue.

[Dhacd]

Try removing the last ")" after the maxindex, If it is still not working please paste the code and I will run it from my side and check again.

[Dhacd · Accepted solution]

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)