Forum Discussion

Advocate III

10 years ago

Solved

DATEADD Function

I'm hoping someone can help me get past this roadblock. It seems what I'm doing is fairly simple and I may be making it more difficult thatn it should be. All I'm trying to do is aggregate the sum o...

Need Help

Greg_Deckler
10 years ago
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.

Greg_Deckler

Community Champion

10 years ago

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.