Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DATEADD not working in Matrix

I created a measure that shows revenue from the 12 months before the last 12 months using DATEADD function.  I put it in a MATRIX and it works fine when there are no row or column headings added to t...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    According to your description, i create a sample to test:

    Then create a date slicer table:

    Date Slicer = DISTINCT(SELECTCOLUMNS('Table',"Date",'Table'[Date]))

    Then try this measure:

    Measure = 
var a = SELECTEDVALUE('Date Slicer'[Date])
return
CALCULATE(SUM('Table'[accrual_revenue]),DATESBETWEEN('Table'[Date],DATE(YEAR(a)-2,MONTH(a),DAY(a)),DATE(YEAR(a)-1,MONTH(a),DAY(a))))

    When choosing one date in date slicer, the result shows the total revenue from the 12 months before the last 12 months based on the date selected:

    Tips: In this case, i add a defined filter to the table visual to show the result more obviously.

    Here is my test pbix file:

    pbix 

    Hope this can help.

     

    Best Regards,

    Giotto Zhi

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

amitchandakThanks.  I do have a date calendar thats joined to my table date.  I believe your formula examples do not pull prior 12 months before the last 12 months.

 

This formula works fine except when a row dimension is added to the matrix.

 

Prior 12 mo Rev = Calculate(sum('TKD'[accrual_revenue]),DATESINPERIOD(TKD[Date],ENDOFMONTH(DATEADD(TKD[Date],-12,MONTH)),-12,MONTH))

 

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous 

Yes when you view by few dimension not all dates world be there, Try if one of 2 works

 

Prior 12 mo Rev = Calculate(sum('TKD'[accrual_revenue]),DATESINPERIOD(Date[Date],ENDOFMONTH(DATEADD(TKD[Date],-12,MONTH)),-12,MONTH))
Prior 12 mo Rev = Calculate(sum('TKD'[accrual_revenue]),DATESINPERIOD(Date[Date],ENDOFMONTH(DATEADD(Date[Date],-12,MONTH)),-12,MONTH))

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    amitchandakUnfortunately, they didn't work.  I think it's the dateadd piece that breaks it. 😞

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      Anonymous 

      I have one doubt, your calendar min date is less than the min date of the TKD table. Just check.

      If not so, Create a sample where you can reproduce and share.