Hi,
I have the following data:
Financial Statement Account | Date | Amount |
---|---|---|
A | 1-1-2018 | 5 |
A | 1-2-2018 | 10 |
B | 1-1-2018 | 20 |
B | 1-2-2018 | 10 |
I am creating a pivot, with Date (hierarchy) as columns and Financial Statement Account as Rows.
When 'zooming out' from date to Quarter or Year for certain rows I would like to take the value of the last date in that quarter, So for example, in case of data above. I would like to take SUM for A and Last value for B
Year | 2018 |
---|---|
Quarter | Q1 |
Financial Statement Account | |
A | 15 |
B | 10 |
How can this be done?
Thanks in advance.
Hi bananalyst,
Obviously, for the SUM for A, a simple SUM( [Amount] ) would work.
As for the Last Value for B, a LOD expression would help, like this:
IF [Date] = { EXCLUDE [Date] : MAX( [Date] ) }
THEN [Amount]
END
The above should be brought to a view
as an Aggregation ( MIN / MAX / AVG).
Yours,
Yuri