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.

 

[luirib]

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]

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.