YTD calculations when using SSAS as datasource

[FlaBusInt]

I have a cube that tracks sales by sales rep. In this cube, I have dimensions for SalesRep, Product, and Region hierarchies. I also have a Time dimension that provides Fiscal Year, Fiscal Quarter, Fiscal Month, and Calendar Date; the Time dimension also has an attribute showing the first day of the year for any given date (our fiscal year starts on a different date every year).

I have a report that passes StartDate and EndDate parameters back to the cube, and provides sales numbers by Rep, Product, and Region for that given date range. What I would also like is a field that provides YTD sales through the EndDate parameter.

This is a piece of cake for me to implement against the SQL tables, but I am pulling my hair out trying to determine the best way to implement with a cube. Does anyone have any suggestions?

TIA

- Steve

 

[SQLBI]

Hi,

You will need to store YTD/Periodic aggregates in your cube and then bring them back as part of your dataset, RS has no inbuilt methods that i'm aware of.

You'll need to use the YTD() MDX function or similar, someone here forum960 will be able to help you out


Cheers,
Leigh

"If you had one shot, one opportunity, to seize everything you ever wanted, in one moment, would you capture it, or let it slip?" - Eminem