# Totalytd



## luirib (Nov 27, 2012)

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 (Nov 27, 2012)

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 (Nov 27, 2012)

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 (Nov 27, 2012)

ok so I see this

Excel 2010ABCD1MonthYTD2Revenue3RevenueSouth1001,2004RevenueNorth1252,3565RevenueWest2154,1366RevenueEast117856Fact Table

Excel 2010AB1DatePeriod26/5/20122012-6MyDate

but I don't see a relationship between the two


----------



## texasalynn (Nov 27, 2012)

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

Excel 2010AB1DatePeriod26/5/20122012-6MyDateCell FormulasRangeFormulaB2=+A2


----------



## texasalynn (Nov 27, 2012)

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 (Nov 28, 2012)

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 (Nov 28, 2012)

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 (Nov 29, 2012)

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 (Nov 29, 2012)

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


----------



## luirib (Nov 27, 2012)

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.


----------



## luirib (Nov 30, 2012)

Thanks for keeping the thread alive. I'm at wits end with this issue.

The problem with using the date from the Fact table is that you do not get the info for past months if the line item happens to have no data for the current month. Let me illustrate: you have $200 sales in Jan. but no Sales in June. If you run the report YTD for June, the sales line will not show (although the totals will include the $200 for Jan). You solve this issue by creating a separate dates table.

TOTALYTD also has its issues depending on what you select. What I picked up on one of the sites is a more convoluted way to get the YTD figures but I am having a hell of a time making it work. This is the formula:

YTD Actuals = calculate( SUM ( Fact [Amount] ), datesbetween(Dates[Date],Year_Periods[Selected_Year_Start_Date],Year_Periods[Selected_Month_End_Date]))

Dates is the separate dates table I mention above.
Year_Period is a table that has various dates (e.g. month start date, month end date, etc.)
Selected_Year_Start_Date: isolates the first day of the calendar year (in my case Jan 1)
Selected_Month_End_Date: shows the last day of the month for which I am running the reports.

I have tested each of the last 2 lines and the dates given are correct.

The problem I have is that the YTD value is the same as the month info. I have looked at the relationships and cannot find anything wrong there, but I am sure I am missing something.

Please somebody give me a hint as to where I may be going wrong. Thanks again.


----------



## luirib (Dec 1, 2012)

They don't call them golden rules for nothing...I was not overriding the month filter, therefore I was getting just the selected month.

This is how the formula ended:

YTD Actuals = calculate ( sum ( Fact[AMOUNT] ) , ( Dim_Month[Month] ) , datesbetween(Dates[Date],Year_Periods[Selected_Year_Start_Date],Year_Periods[Selected_Month_End_Date]) )

Thanks for your help.


----------

