Forum Discussion
DATEADD Function
Your formula looks very similar to this one here but I suspect that the data model used in that case is different than yours
http://community.powerbi.com/t5/Desktop/Smart-DAX-Last-Year-To-Date/m-p/20099#M6085
Your formula is basically going to filter the dates involved to a specific date, which is probably not what is desired, correct?
I imagine that you are using a measure and that you want to SUM all of the [S2458 - Enrollment Quantity] for the last 12 months inclusive. So, you should be able to do something like:
Enrollment for Previous 12mo - S2458 = CALCULATE(SUM(tbl_FixedCost[S2458 - Enrollment Quantity]),FILTER('tbl_FixedCost', 'tbl_FixedCost'[Billing Date] > DATEADD(TODAY(),-12,MONTH)))Really difficult to give you a definitive answer without knowing your data model, but think of it this way, you need to filter out all of the rows in tbl_FixedCost that have dates greater than Today - 12 months and then SUM the [S2458 - Enrollment Quantity] field for those rows. Your formula, I believe, is basically filtering your table down to a single row, if it exists.
Again, tough to say without more information like whether this is measure context or column context and a little more information about your model. I'm purely speculating here for a large part of this.
Your formula looks very similar to this one here but I suspect that the data model used in that case is different than yours
http://community.powerbi.com/t5/Desktop/Smart-DAX-Last-Year-To-Date/m-p/20099#M6085
Your formula is basically going to filter the dates involved to a specific date, which is probably not what is desired, correct?
I imagine that you are using a measure and that you want to SUM all of the [S2458 - Enrollment Quantity] for the last 12 months inclusive. So, you should be able to do something like:
Enrollment for Previous 12mo - S2458 = CALCULATE(SUM(tbl_FixedCost[S2458 - Enrollment Quantity]),FILTER('tbl_FixedCost', 'tbl_FixedCost'[Billing Date] > DATEADD(TODAY(),-12,MONTH)))Really difficult to give you a definitive answer without knowing your data model, but think of it this way, you need to filter out all of the rows in tbl_FixedCost that have dates greater than Today - 12 months and then SUM the [S2458 - Enrollment Quantity] field for those rows. Your formula, I believe, is basically filtering your table down to a single row, if it exists.
Again, tough to say without more information like whether this is measure context or column context and a little more information about your model. I'm purely speculating here for a large part of this.
You're right, I'm looking an inclusive aggregation of previous 12 months so thank you for that formula, it should work. However, when I try and use it I receive the error for the TODAY() function saying 'The first argument to 'DATEADD' must specify a column'
Caitlin_Knox - I'll have to test for a better solution, but one solution is to create a custom column with a formula of:
Today = TODAY()
Then you can just use that column.
That worked perfect, thank you
What's the recommended approach when you need to use DateAdd for a scalar value other than Today() (i.e. something you can't simply turn into a column)?
