Hi all - I have q question about point in time data in SSAS.
Attached is an example from the DW: I have a unique row (denoted by NID and RID) which has three states at different points in time.
16/05 shows the overall status be Open with a TotalPaid of 406.56.
12/06 shows overall status to be Open with a different TotalPaid of 410.
18/07 shows overall status now to be Closed with same TotalPaid.
What I would like to do in the cube is upon filtering on a date tied to RunDate column, I would like to show the latest record at a given time - for example if I selected 14/06/2014 in a filter, I would get the row with RunDate 12/06/2014 (since this is
the latest row at that time).
There is no need for summing data, as the TotalPaid column is already doing the aggregation.
Think of this as a SCD type 2, you need to have valid_from and valid_to date and filter in between.
for example: WHERE Given_Point_In_Time BETWEEN valid_from AND valid_to
That would give you the current status at a given time
Microsoft Certified Trainer & MVP on SQL Server Please "Propose as Answer" if you got an answer on your question, and vote for it as helpful to help other user's find a solution on a similar question quicker.