Measure respect to the previous of a selected year

Question

Hi.

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

Answer 1

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

---

Javier Guillen
http://www.msbicentral.com/Blogs/JavierGuillen.aspx

Answer 2

Hi 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

Answer 3

Psorca,

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

Otherwise, if you have only years and months, a simpler formula might be something like:

=IF (
    COUNTROWS (VALUES (Fact[YEAR])) = 1,
    CALCULATE (
        SUM (Fact[Amount]),
        Fact[Year] = VALUES (Fact[Year]) - 1
    ),
    BLANK ()
)

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.

---

Alberto Ferrari
http://www.powerpivotworkshop.com

Answer 4

Psorca,

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)

---

Eddy N.

Answer 5

Hi,

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.

Many thanks.