Totalytd

[luirib]

I am stumped with what seems to be a simple formula. I need to create a measure that calculates the YTD totals for each row. This is what I have:

TOTALYTD ( sum ( Fact[Amount] ) , 'Date'[Date] , ALL ('Date'))

'Date'[Date] is the table where I store all possible dates and months.

The problem I am getting is that this formula is showing totals for all the periods I have on the database, not just for the current year or up to the month I'm running the report to (e.g., if I run a report for March 2012, it shows the info for 2010, 2011 and thru Dec of 2012). It must be a filter that I am not applying, but I cannot make sense out of it. Dec 31 is our year end.

Any lead will be greatly appreciated.

 

[texasalynn]

is not clear about your data. THe total for the row? Are there multiple columns with different dates/years? Would you post a sample of your data layout? If you can't use one of the html apps to post your data, you can put borders around your data and then copy/paste it into your post.

 

[luirib]

What I'm building is an Income Stmt. This is the layout:
Month YTD
Revenue
Revenue South 100 1,200
Revenue North 125 2,356
Revenue West 215 4,136
Revenue East 117 856

Month is a column of the Fact table.
YTD is a calculated measure where I put the formula that is not working.

Revenue is the first level of filter I apply in row labels.
Revenue South/North/West/East is the second filter I apply in row labels.

My Date table has 2 columns as follows:
Date: M/D/YYYY (Date format)
Period: YYYY-M (Text format)

Fact table and Date tables have a relationship b/w Transaction date and Date.

Is this enough info? Thanks.

 

[texasalynn]

ok so I see this

Excel 2010
	A	B	C	D
1			Month	YTD
2	Revenue
3	Revenue	South	100	1,200
4	Revenue	North	125	2,356
5	Revenue	West	215	4,136
6	Revenue	East	117	856
Fact Table

Excel 2010
	A	B
1	Date	Period
2	6/5/2012	2012-6
MyDate

but I don't see a relationship between the two

 

[texasalynn]

BTW the second table make the period a formula and format as yyyy-d

Excel 2010
	A	B
1	Date	Period
2	6/5/2012	2012-6
MyDate

Cell Formulas
Range	Formula
B2	=+A2

 

[texasalynn]

oh never mind I see that this is a PowerPivot question - forget what I just posted. I don't like the "zero reply" link

 

[mdrew9]

Hard to tell what your filters are but my formula is =CALCULATE([Actuals],DATESYTD(qryActuals[Date]),all(Dates[Month])) so change your DimDates table to FactDates.

 

[luirib]

Mdrew9, thanks for replying. This link discusses the need to have a separate date table (see rule 2). I have run into problems for using the dates on the Fact table.

Kasper de Jonge PowerPivot Blog » PowerPivot Time intelligent functions golden rules

I'll keep trying and post if I find out what the issue is.

 

[mdrew9]

What happens when you take out ALL ('Date')? You only really need to use the second argument if you have an alternative Year end date.

 

[mdrew9]

Total FactAmtYTD = TOTALYTD ( sum ( Fact[Amount] ) , 'Date'[Date] )