Need Help With Dax

[jgarden6]

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

[PhilipTreacy · Accepted solution]

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

[jgarden6]

it worked .  thank you so much.

[pranit828]

HI @jgarden6 

Create a measure as below

measure1 = CALCULATE(SUM('table'[column]),FORMAT('table'[date],"YYYY") = "2012")

[jgarden6]

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

[PhilipTreacy]

@jgarden6 

Please see my response below.

Phil

[jgarden6]

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 parsing

Can you see what I'm doing wrong

[PhilipTreacy]

Hi @jgarden6 

You're missing the closing ) for SUM

measure1 = CALCULATE( SUM('Exported_Data'[Taxable Value] ) , FORMAT('Exported_Data'[date],"YYYY") = "2012")

Regards

Phil

[jgarden6]

That fixed the measure but I still don't get that value to show up on the other rows for the year.  What I need to do is to divide each year that is summed on a column divided by the base year (2012) summed on the same variable.  This will give me the growth for each year from the base year 2012.