Monday, May 16, 2011 11:07 AM
I'm trying to determine the sum of Invoices for a selected year. I need to create a new measure respect to the previous selected year. Year is a dimension.
Any helps to do it, please? Many thanks
Monday, May 16, 2011 4:13 PM
In order to get the aggregate from the prior year, you can use the PARALLELPERIOD function. Something like:
=CALCULATE( SUM(FactResellerSales[SalesAmount]), ALL(FactResellerSales), PARALLELPERIOD(DimDate[FullDateAlternateKey], 1, YEAR) )
Tuesday, May 17, 2011 9:42 AMHi Javier, but if I have only year and month in the fact table? Do I need to have to create a date and a time dimension? Thanks
Tuesday, May 17, 2011 10:29 AMPsorca,All time intelligence functions in PowerPivot/DAX require a calendar table as mandatory. If you don't have such a table, time intelligence functions might return wrong values. Take a look at: http://www.powerpivotblog.nl/powerpivot-time-intelligent-functions-golden-rulesOtherwise, if you have only years and months, a simpler formula might be something like:=IF (
COUNTROWS (VALUES (Fact[YEAR])) = 1,
Fact[Year] = VALUES (Fact[Year]) - 1
)Nevertheless, I strongly suggest you to take a look at the many sources on the web which describe how to implement time intelligence, adding a calendar table is strongly reccomended as it leads to a better user experience since you can handle quarters, fiscal years and so on.
- Marked As Answer by pscorca Tuesday, May 17, 2011 4:16 PM
Tuesday, May 17, 2011 12:09 PMPsorca,You can easily create a date dimension with next expressions:- in case of one column with data formatted like yyyymm=DATE(LEFT([YearMonth],4),RIGHT([YearMonth]),1)- in case of two columns with year and month=DATE([Year],[Month],1)
Tuesday, May 17, 2011 4:16 PM
also I think to create a date in the fact table and a date dimension, but I'd like to skip a passage because I need to load data already aggregated by year and month.
All your replies are good and interesting answers, but in this case I think that Alberto provides me the reply more useful.