Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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-...
Best Regard
firstch
Solved! Go to Solution.
@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
@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
)
@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
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
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 @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?
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).
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |