Measures for Monthly Actuals using DATEADD

[Jerid421]

I am trying to create measures with the DATEADD function for each month of the present year. I am doing this by passing a THISMONTH mueasures into the DATEADD to calculate how many time periods to step back to get each month. All future months will be "0" as there are actuals in future months: For example:

JAN:=IF(-([THISMONTH]-1)>0,0,
CALCULATE(SUM(fact_ActualsOrders_ES[amount]),DATEADD(fact_ActualsOrders_ES[actuals_date],-([THISMONTH]-1),MONTH)))

FEB:=IF(-([THISMONTH]-2)>0,0,
CALCULATE(SUM(fact_ActualsOrders_ES[amount]),DATEADD(fact_ActualsOrders_ES[actuals_date],-([THISMONTH]-2),MONTH)))

The SUM on [amount] is returning very, very high (and incorrect) for the first (3) months I've tried.

What am I doing wrong? And advice?

Thanks!!!

 

[scottsen]

Picture isn't showing for me, but I have 1 question, and 1 statement:

1) where do you use this -- eg, what is the filter context when this measure is evaluated? eg, what are the dates associated with fact_ActualsOrders_ES[actuals_date] ?
If you just evaluate that with no filter context (drop it on values of a pivot table with no date rows), its going to be all the dates... so, even if you shift it by a month or whatever, it is still a lot of dates.

2) It's weird and likely wrong to use DATEADD against the FACT table. DATEADD should be used against a Calendar lookup table.

 

[Jerid421]

Picture isn't showing for me, but I have 1 question, and 1 statement:

1) where do you use this -- eg, what is the filter context when this measure is evaluated? eg, what are the dates associated with fact_ActualsOrders_ES[actuals_date] ?
If you just evaluate that with no filter context (drop it on values of a pivot table with no date rows), its going to be all the dates... so, even if you shift it by a month or whatever, it is still a lot of dates.

2) It's weird and likely wrong to use DATEADD against the FACT table. DATEADD should be used against a Calendar lookup table.

Yeah... well what happened is that I couldn't get the DATEADD to work against the Dim_Date table, so I used the RELATED function in the ActualsOrders_ES table to create a calculated column called [actuals_date]. That's why I've been pointing the DATEADD to it.

The measures that I'm creating are calculating correctly now, issue #1 is kind of moot. But now when I try to add the [JAN] measure to the pivot I get an error:

[TABLE="width: 1018"]
<tbody>[TR]
[TD="class: xl65, width: 1018"] ERROR - CALCULATION ABORTED: Calculation error in measure 'fact_ActualsOrders_ES'[JAN]: Function 'DATEADD' only works with contiguous date selections. [/TD]
[/TR]
</tbody>[/TABLE]


Which I'm sure is tied back to me not using the dim_Date table. : (

 

[Jerid421]

Yeah... well what happened is that I couldn't get the DATEADD to work against the Dim_Date table, so I used the RELATED function in the ActualsOrders_ES table to create a calculated column called [actuals_date]. That's why I've been pointing the DATEADD to it.

The measures that I'm creating are calculating correctly now, issue #1 is kind of moot. But now when I try to add the [JAN] measure to the pivot I get an error:

[TABLE="width: 1018"]
<tbody>[TR]
[TD="class: xl65, width: 1018"] ERROR - CALCULATION ABORTED: Calculation error in measure 'fact_ActualsOrders_ES'[JAN]: Function 'DATEADD' only works with contiguous date selections.[/TD]
[/TR]
</tbody>[/TABLE]


Which I'm sure is tied back to me not using the dim_Date table. : (

Even when I try to use it with a YTD measure, it returns a BLANK:

YTDActuals:=Calculate(SUM(fact_ActualsOrders_ES[amount]),DATESYTD(dim_date[calendar_date]))

But the correct value is calculated if I use:

YTDActuals:=Calculate(SUM(fact_ActualsOrders_ES[amount]),DATESYTD(fact_ActualsOrders_ES)[actuals_date]))

 

[scottsen]

Hmmm...

Ya, you want to work against your dim_date, but I can't say offhand why that isn't working. They are related and the arrow points at dim_date ?

Any chance you can share your workbook (google drive, dropbox, whatever) ?

 

[Jerid421]

I put it into a Box folder for you. You should receive an invite. Used scott "at" tinylizard "dotcom".

 

[scottsen]

Received. Can you drop me an email please? I think you have lost your way...