Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
AlanP514
Post Patron
Post Patron

Previous Version Value Dynamic

Screenshot_54.png
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
  



3 ACCEPTED SOLUTIONS

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.



View solution in original post

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)

View solution in original post

tamerj1
Super User
Super User

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
    )

1.png

 

View solution in original post

11 REPLIES 11
tamerj1
Super User
Super User

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
    )

1.png

 

Super Bro , amazing soution

Dhacd
Resolver III
Resolver III

@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

AlanP514_0-1654752896883.png

 

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.



AlanP514_1-1654755870390.png

Hai @Dhacd 
Can you check this

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

AlanP514_0-1654754750471.png

 

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

Dhacd_0-1654754882284.png

 

Hai @Dhacd 

AlanP514_0-1654755193106.png

Again there is a syntax issue.

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_0-1654755409897.png

 

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)

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors