DATEADD Function

[Caitlin_Knox]

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 of the column [S2458 - Enrollment Quantity] for the past 12 months. I currently only have one month's data in the database. Could this be why? Since its not counting the current month? If that's the case, when more data is inputed, will this formula work for what I need?

Enrollment for Previous 12mo - S2458 = CALCULATE(SUM(tbl_FixedCost[S2458 - Enrollment Quantity]),DATEADD(tbl_FixedCost[Billing Date],-12,MONTH))

[Greg_Deckler · Accepted solution]

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.

[Zwakele]

I clueless where to start. I happy to provide more info. Needed.

I have a report I want to show current year by month total and I want to compare values with the previous years by month.

On Excel, I used the Value Field Settings to show values as Difference from Based field [Year] and Base items [next]

[Caitlin_Knox]

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'

[Greg_Deckler]

@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.

[Anonymous]

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)? 

[vanessa]

@Greg_Deckler

I tried creating a custom column for TODAY().

But DATEADD returns blank.

[Caitlin_Knox]

That worked perfect, thank you