Forum Discussion
Need Help With Dax
I need help with Dax. I'm new to Dax so excuse my lack of knowledge. I have data that I want to sum a column for each year. I have data for years 2012 through 2020. I wnat a measure that shows the 2012 sum in a column for each year. I then can caluculate the growth rate from the base 2012 sum for each year. ie) 2012 to 2012, 20012 to 2013, 20212 to 2014, etc. I can get the sum for each year but I need the 2012 amount repeated for each yearly sum so I can c
Hi jgarden6
Download sample PBIX with measures and data shown below.
I'm not sure what your data looks like - are you storing the year as text, a number or as a date?
But if your Year column is numerical and just holds the year then try this measure
Measure = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'), 'Table'[Year] = 2012))If you have a column of dates then use this measure
Measure 2 = CALCULATE(SUM('Table2'[Value]),FILTER(ALL('Table2'), YEAR('Table2'[Date]) = 2012))Regards
Phil
8 Replies
Hi jgarden6
Download sample PBIX with measures and data shown below.
I'm not sure what your data looks like - are you storing the year as text, a number or as a date?
But if your Year column is numerical and just holds the year then try this measure
Measure = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'), 'Table'[Year] = 2012))If you have a column of dates then use this measure
Measure 2 = CALCULATE(SUM('Table2'[Value]),FILTER(ALL('Table2'), YEAR('Table2'[Date]) = 2012))Regards
Phil
it worked . thank you so much.
HI jgarden6
Create a measure as below
measure1 = CALCULATE(SUM('table'[column]),FORMAT('table'[date],"YYYY") = "2012")
- measure1 = CALCULATE(SUM('Exported_Data'[Taxable Value],FORMAT('Exported_Data'[date],"YYYY") = "2012")I must be doing something wrong. I'm getting a message "Too many arguments...something abour parsingCan you see what I'm doing wrong
Hi jgarden6
You're missing the closing ) for SUM
measure1 = CALCULATE( SUM('Exported_Data'[Taxable Value] ) , FORMAT('Exported_Data'[date],"YYYY") = "2012")Regards
Phil
I had a syntax issue so I fixed that. It shows the summed value for 2012 but it is blank for all the other rows. I'm summing by year. When I do my divide (base year 2012 / each subsequent year , I get blanks for 2013 thru 2020 and that is because the 2012 sum is not shown for each year. Help again
