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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
firstch
Helper III
Helper III

Is there any way to get last to of value in slicer that is text, then convert to int and use in dax

Hi all ,

Is there any way to get last to of value in slicer that is text, then convert to int and use in this DAX

 

firstch_0-1660539774328.png

my sample data please refer to this post:https://community.powerbi.com/t5/DAX-Commands-and-Tips/Is-there-any-way-to-get-Last-Year-value-with-...

 

firstch_1-1660539939046.png

Best Regard
firstch

3 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

@firstch 
Here is a sample file with the solution https://www.dropbox.com/t/SDqoISRmW1vgAF0a

Value Last Year = 
VAR SelectedProductionYear = MAX ( 'Table A'[productionYear] )
VAR SelectedYear = VALUE ( RIGHT ( SelectedProductionYear, 2 ) )
VAR LastProductionYear = LEFT ( SelectedProductionYear, 2 ) & SelectedYear - 2 & "/" & SelectedYear - 1
VAR Result =
    CALCULATE ( 
        SUM ( 'Table A'[value] ),
        'Table A'[productionYear] = LastProductionYear
    )
RETURN
    Result

View solution in original post

@firstch 
Sorry, I was not on my desk. Here is the updated file https://www.dropbox.com/t/oNRuMlL6NSzUdJhu

Value Last Year = 
VAR SelectedProductionYear = MAX ( 'Table A'[productionYear] )
VAR SelectedYear = VALUE ( RIGHT ( SelectedProductionYear, 2 ) )
VAR LastProductionYear = LEFT ( SelectedProductionYear, 2 ) & SelectedYear - 2 & "/" & SelectedYear - 1
VAR Result =
    CALCULATE ( 
        SUM ( 'Table A'[value] ),
        'Table A'[productionYear] = LastProductionYear
    )
RETURN
    IF (
        COUNTROWS ( ALLSELECTED ( 'Table A'[productionYear] ) )
            <> COUNTROWS ( ALL ( 'Table A'[productionYear] ) ),
        Result
    )

View solution in original post

Hi @tamerj1  Thankyou so much for your help, 

firstch_0-1660555120226.png

Best Regard

firstch

 

View solution in original post

10 REPLIES 10
tamerj1
Super User
Super User

@firstch 
Here is a sample file with the solution https://www.dropbox.com/t/SDqoISRmW1vgAF0a

Value Last Year = 
VAR SelectedProductionYear = MAX ( 'Table A'[productionYear] )
VAR SelectedYear = VALUE ( RIGHT ( SelectedProductionYear, 2 ) )
VAR LastProductionYear = LEFT ( SelectedProductionYear, 2 ) & SelectedYear - 2 & "/" & SelectedYear - 1
VAR Result =
    CALCULATE ( 
        SUM ( 'Table A'[value] ),
        'Table A'[productionYear] = LastProductionYear
    )
RETURN
    Result

hi @tamerj1 

 thankyou ,its work for me now, but if it possible that when user

firstch_1-1660549349966.png

 

 

 select all in filter can you make the lastyear card to be blank?

 

best regard

 

firstch

Hi @firstch 

do you mean in case of multiple selections or only in the case where all the filter is selected?

HI

@tamerj1 

in case of all filter ,currently when select all its show data of year 2564/65 sir 

 

best regard

firstch

@firstch 
Sorry, I was not on my desk. Here is the updated file https://www.dropbox.com/t/oNRuMlL6NSzUdJhu

Value Last Year = 
VAR SelectedProductionYear = MAX ( 'Table A'[productionYear] )
VAR SelectedYear = VALUE ( RIGHT ( SelectedProductionYear, 2 ) )
VAR LastProductionYear = LEFT ( SelectedProductionYear, 2 ) & SelectedYear - 2 & "/" & SelectedYear - 1
VAR Result =
    CALCULATE ( 
        SUM ( 'Table A'[value] ),
        'Table A'[productionYear] = LastProductionYear
    )
RETURN
    IF (
        COUNTROWS ( ALLSELECTED ( 'Table A'[productionYear] ) )
            <> COUNTROWS ( ALL ( 'Table A'[productionYear] ) ),
        Result
    )

Hi @tamerj1  Thankyou so much for your help, 

firstch_0-1660555120226.png

Best Regard

firstch

 

tamerj1
Super User
Super User

Hi @firstch 
In your code: LEFT ( 'table a'[custom], 2 ) will return 25 for all the rows in the sample data. Would you please provide the eaxpected result as per the same sample data?

dear @tamerj1 

it would be RIGHT ( 'table a'[custom])

 

best regard

firstch

daXtreme
Solution Sage
Solution Sage

@firstch 

 

Generally, it's a bad idea. There's a much better one. In the table from which your slicer's values come put another column where the entries will already be in the right format and type. You can hide this column as it's going to be  meant for your measure(s) only. Then you'll be able to retrieve the number straight from the table instead of calculating it and wasting CPU cycles (making DAX slower and more complex than it needs to be).

@daXtreme  thankyou for your suggestion 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors