Time Intelligence Function Help

cmajka

Board Regular
Joined
Mar 18, 2013
Messages
175
Hi All -

I have created a lookup calendar table and am using the following formula, however, I am getting an error:

Total DPW YTD:=CALCULATE([DPW],DATESYTD (dimDate[Date]))

Any suggestions?

The error states...'Semantic Error: the value for 'DPW' cannot be determined. Either 'DPW' doesn't exist, or there is no current row for a column named 'DPW'

I'm not quite sure i understand the time intelligence functions yet, so bear with me.

Thanks!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hi Matt - DPW is indeed a column. I wrapped it in Sum, and the formula no longer returns an error. However, when I drop that new YTD measure into the pivot, it doesn't seem to be adding up each month's value for the YTD figure, rather, it just shows the value for that month. So if DPW for JAN, FEB, MAR show 59,75, and 105...the DPW YTD column shows 59,75, and 105. I would expect it to show 59, 134, and 239. Perhaps this has to do with the date field I am using in my YTD measure?
 
Upvote 0
The calendar table is linked on a field called Date, and it is broken down by day of the year and time stamp, and looks like 1/1/2014 12:00:00 AM. This is linked to my Table1, which uses a field called monthyear and reads like January, 2014. When I click on one of these cells in the Table1, it looks like 1/1/2014 12:00:00 AM. In this table I also have a field Month and a field Year that I could possibly use. Thanks
 
Upvote 0
Perfect - looks like that was the issue. I was using month and year in the pivot table from my Table1. I switched it out with the month and year from the Calendar table and now the column is adding up correctly on the pivot. Thanks so much for the help. I am eager to use power pivot and query in our everyday duties...this is a first step. Thanks again!
 
Upvote 0
Hi Matt - building on this, would you recommend using the Dateadd function for YOY Growth? When using the follwoing, i am getting an 'Expression is not valid..." error

Pct PIF Growth YOY:=CALCULATE(SUM([PIF]))-CALCULATE(SUM([PIF]),DATEADD(dimDate[Date],-1,YEAR))/CALCULATE(SUM([PIF]),DATEADD(dimDate[Date],-1,YEAR)

Anything pops out to you on this one or would you recommend going with Sameperiodlastyear function?

Thanks!
 
Upvote 0
Is [PIF] a column? You should always place a table name in front the of a column name. Try this (replace my table name)

=
SUM ( table[PIF] )
- (
CALCULATE ( SUM ( table[PIF] ), DATEADD ( dimDate[Date], -1, YEAR ) )
/ CALCULATE ( SUM ( table[PIF] ), DATEADD ( dimDate[Date], -1, YEAR ) )
)
 
Upvote 0
Thanks Matt - this works great!

The calculation seemed off a bit so I changed some of the parens around - I think it was the order of ops

PIF Growth YOY:=(SUM ( Table1[PIF] ) - CALCULATE ( SUM ( Table1[PIF] ), DATEADD ( dimDate[Date], -1, YEAR )))/ CALCULATE ( SUM ( Table1[PIF] ), DATEADD ( dimDate[Date], -1, YEAR ))

Thanks again for all your help
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top