Problem with comparing Previous month/years in DAX

[CopperKnife]

Hi all,
I have a Date table and Fact table.

Cols in Date table:
Date
Month
Qtr
Year
MonthName
QtrName
YearName

Cols in Fact table:

Date
Product
Country
Region
Qty

In DAX, I need to write an expression for calculating the following:

(JanPrevYear + FebPrevYear / JanCurrYear+ FebCurrYear)-1

in the next coloumn

(JanPrevYear + FebPrevYear +MarPrevYear / JanCurrYear+ FebCurrYear+MarCurrYear)-1

in the next coloumn

(JanPrevYear + FebPrevYear +MarPrevYear+AprPrevYear / JanCurrYear+ FebCurrYear+MarCurrYear+AprCurrYear)-1

it goes on increasing the no of months when it goes forward, I am wondering what DAX function should I use and How??

I can use neither the plain MTD/YTD nor SAMEPERIODLASTYEAR here, because the no of months are going to be increased as it goes forward.

Please help me.
CopperKnife

 

[Matt Allington]

Well you shouldn't be using columns for this. As the number of columns increase, the file will get increasing larger and slower. You should write measures. Secondly you should consider using 1 measure and then use the month names to create the running total.

You should then add an ID column to your date table. Just add integers starting from 1 and increasing by 1 for each month.

You will need something like these

total sales year to date = calculate(sum(factTable[Qty], filter(all(calendar),calendar[ID]<=max(Calendar[ID]) && calendar[year]=max(calendar[year])))


total sales year to date last year = calculate(sum(factTable[Qty], filter(all(calendar),calendar[ID]<=max(Calendar[ID])-12 && calendar[year]=max(calendar[year])-1))

change YTD vs LY = ([Total Sales Year To Date]/[]total sales year to date last year)-1

put calendar month in rows on your pivot, and these should work.

for an explanation of how these work, have a look at this post I did yesterday at PowerPivotForum.com.au
PowerPivot Forum • View topic - Month Over Month

 

[CopperKnife]

Thanks for your reply, Matt.

But I have written something like this:

TotalSales:=CALCULATE(SUM(FactSales[Units]))

YTDSales:=CALCULATE([TotalSales],DATESYTD(dCalendar[Date]))

PYTDSales:=TOTALYTD([TotalSales],SAMEPERIODLASTYEAR(dCalendar[Date]))

YTDGrowth:=[YTDSales]/[PYTDSales]-1

Can you scrutinize these expressions, please. I think it should work.

CopperKnife

 

[Matt Allington]

Can you scrutinize these expressions, please. I think it should work.

CopperKnife

Not sure about this one. If it works, it works. But I would do it this way

PYTDSales:=CALCULATE([YTDSales],SAMEPERIODLASTYEAR(dCalendar[Date]))