Measures for Monthly Actuals using DATEADD

Jerid421

New Member
Joined
Jan 10, 2014
Messages
12
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!!!
 
Last edited:

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
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

New Member
Joined
Jan 10, 2014
Messages
12
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:

ERROR - CALCULATION ABORTED: Calculation error in measure 'fact_ActualsOrders_ES'[JAN]: Function 'DATEADD' only works with contiguous date selections.

<tbody>
</tbody>


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

Jerid421

New Member
Joined
Jan 10, 2014
Messages
12
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:

ERROR - CALCULATION ABORTED: Calculation error in measure 'fact_ActualsOrders_ES'[JAN]: Function 'DATEADD' only works with contiguous date selections.

<tbody>
</tbody>


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

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
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

New Member
Joined
Jan 10, 2014
Messages
12
I put it into a Box folder for you. You should receive an invite. Used scott "at" tinylizard "dotcom".
 

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
Received. Can you drop me an email please? I think you have lost your way... :)
 

Watch MrExcel Video

Forum statistics

Threads
1,100,203
Messages
5,473,138
Members
406,847
Latest member
Meow

This Week's Hot Topics

Top