Forum Discussion
DAX Statement for current year only
I have dashboard built off underlying excel spreadsheets which have ytd expenditures and forecasted expenditures future month expenditures. I have pulled this into a table that also includes 2018 actuals. I'm trying to create a dax measure that calculates the sum of expenditures only for the current year. I have the following formula, which works, but I'm prefer it to be dynamic, so next year I don't have to re-write it for the following year's dates
[Calculated Expenditures and Forecast] = sum of my expenditures and forecast column
2019 Year-End Forecasted Expenditures = CALCULATE([Calculated Expenditures and Forecast], DATESBETWEEN('Expenditure/Forecast and Budget'[Date], date(2019,1,1), date(2019,12,31)))
Thanks in advance!
1 Reply
Anonymous
There are many ways to specify the current year. One way is to use the values of your date dimension table in the year colume with a measure like:
DimDate[Year]) = YEAR(TODAY())You can also use the DAX for Start of Year:STARTOFYEAR(<dates>)and End of Year:ENDOFYEAR(<dates> [,<year_end_date>])Use these in your formula to calculate the appropriate start and end points to create a dynamic calculation.Resources:
