Forum Discussion

samnaw's avatar
samnaw
Resolver I
6 years ago
Solved

Convert sting to integer Calculate

 

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

 

 

Need Help
Show and Tell
Tips and Tricks
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

3 Replies

  • mohammedadnant's avatar
    mohammedadnant
    Impactful Individual
    6 years ago

    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

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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,