Forum Discussion

AlanP514's avatar
AlanP514
Post Patron
4 years ago
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 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-2021V5-May-2021
V7-July-2021V6-June-2021
V1-January-2022V12-December-2021
  



Need Help
Show and Tell
Tips and Tricks
  • Dhacd's avatar
    Dhacd
    4 years ago

    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's avatar
    Dhacd
    4 years ago

    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's avatar
    tamerj1
    4 years ago

    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
    )

     

11 Replies

  • Dhacd's avatar
    Dhacd
    Resolver III
    4 years ago

    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's avatar
      AlanP514
      Post Patron
      4 years ago


      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's avatar
        Dhacd
        Resolver III
        4 years ago

        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.



  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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
    )