Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi
I have a year column that I want to keep it as string.
I am currently using this DAX that works perfectly well.
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
Solved! Go to 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,
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,
Use VALUE instead of INT
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
65 | |
63 | |
52 | |
37 | |
36 |
User | Count |
---|---|
82 | |
67 | |
61 | |
46 | |
45 |