Looking to add a time based calculation

[cthurston]

Hello I am trying to show a lead funnel within a matrix.  All fields except for date are measures.  Quote goal is a measure based of win goal, but it takes about 2 months for a quote to turn into a win.  So I need the quote goals to align back 2 months.  Any suggestions on how ot do this?

[Anonymous]

The general format for calculateing measures in different time period is to use DATEADD see below for an example to calculate a rest one month in the past

Measure Last Month = CALCULATE([Measure],DATEADD(DateTable[Date],-1,Month))

[cthurston]

Hello Thomas I am not getting the desired result.  Am I correct in thinking at 0 the value should be the same value?  I'm getting a bunch of 0s and a few innacurrate numbers.

[nickchobotar]

@cthurston

The value will not be the same at 0.  If you want that,  you will have to add it with conditional logic  -  if statements , etc..

N -

[cthurston]

So maybe I'm confused as to what the dateAdd statement does.  Since CALCULATE([Mesure],DATEADD(Table[Date],0,MONTH) dosen't just give me the [Measure] value what is it doing?

[nickchobotar · Accepted solution]

Hi @cthurston

If [Measure] values does not equal to the return scalar value of CALCULATE ( [Mesure], DATEADD ( Table[Date], 0, MONTH ) ) then  most likely the issue is with date table . DATEADD only works with contiguous selection of dates.

To test this: 

1) You can create a new table and run just one dax function CALENDARAUTO() this will create a new date table with one field of consecutive dates .  

2) Create new relationship between newly created date table and your sales (fact) table

3) Use this table as your first argument "Table[Date]" in DATEADD. 

If DATEADD starts to then you need to tweak your original date dimension.

Thanks, Nick

[cthurston]

Perfect this worked thank you!