Time Intelligence Function Help

[cmajka]

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!

 

[Matt Allington]

Is DPW a measure or a column? If a column, wrap it in sum()

 

[cmajka]

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?

 

[Matt Allington]

How is your data table joined to your Calendar table? are you using a surrogate key? Try marking your calendar table as a date table. Read about that here
Power Pivot Calendar Tables - Excelerator BI

 

[cmajka]

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

 

[Matt Allington]

Which date column so you have in your pivot? Does it come from e calendar table? If not, it should

 

[cmajka]

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!

 

[cmajka]

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!

 

[Matt Allington]

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 ) )
)

 

[cmajka]

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