Convert sting to integer Calculate

[samnaw]

Hi 

I have a year column that I want to keep it as string. 

I am currently using this DAX that works perfectly well. 

CY_Family_Sales = CALCULATE (

SUM ( Table_1[Sales]),

FILTER ( 'Table_1', 'Table_1'[Fiscal_Year] = "2020" ),

FILTER ( 'Table_1', 'Table_1'[loyalty_flag] = "CARD" ) )

I want to change year to integer. 

I have tried this: FILTER ( 'Table_1', 'Table_1'[Fiscal_Year] = MAX((INT('TABLE_1'[Fiscal_Year]))-1) )

I have tried this: FILTER ( 'Table_1', 'Table_1'[Fiscal_Year] = MAX((Value('TABLE_1'[Fiscal_Year]))-1) )

but no luck. 

Can someone give me a direction?

Thanks, 

Sam

[v-lid-msft · Accepted solution]

Hi @samnaw ,

We should use max function first, then use value function to convert it to whole number, please try the following measure:

LY_Family_Sales =
VAR LY =
    VALUE ( MAX ( 'Table_1'[Fiscal_Year] ) ) - 1
RETURN
    CALCULATE (
        SUM ( Table_1[Sales] ),
        'Table_1'[Fiscal_Year] = "" & LY,
        'Table_1'[loyalty_flag] = "CARD"
    )

Best regards,

[Greg_Deckler]

Use VALUE instead of INT

[mohammedadnant]

Hi,

First, why you are keeping the Year column in STRING..?, change the Year column to INT/Whole Number,

If you want to keep that column to STRING,

try using these 2 DAX formulas

1. FORMAT( "2020", "General Number")

2. Value("2020")

Hope this will help..

Thanks & Regards,

Mohammed Adnan