Forum Discussion

Caitlin_Knox's avatar
Caitlin_Knox
Icon for Advocate III rankAdvocate 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...
  • Greg_Deckler's avatar
    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.