SSAS, How to create a calculated measure to aggregate latest distinct value for each period

0
votes

I have following measure table:

==================================
|DateKey |Client|VoucherID|Amount|
==================================
|20170101|A     |V1       |100   |
|20170108|A     |V1       |120   |
|20170109|A     |V2       |150   |
|20170111|A     |V2       |130   |
|20170120|B     |V3       |210   |
|20170210|A     |V1       |110   |
|20170215|A     |V4       |200   |
==================================

It is related to client and date dimension. How do I create a calculated measure so that when I create a pivot in Excel only amount of the latest distinct voucher is calculated in each period, as shown in the following table:

=============================
|Month        |Client|Amount|
=============================
|January 2017 |A     |250   | Only sums the latest V1 and V2 (120 + 130)
|January 2017 |B     |210   |
|February 2017|A     |310   |
=============================

Thank you in advance.

1
ssasmdxolap
what have you tried? can you provide details please – whytheq
There is the LastNonEmpty aggregation, but you have to change your measure table anyway. – Danylo Korostil
just tried LastNonEmpty aggregation, just realized that i have standard edition of sql server and LastNonEmpty is not allowed in that edition. is there any work around? thank you. – Normansyah Kusuma
I've seen people in the past use the TAIL function in conjunction with a date dimension to achieve similar – MrHappyHead

1 Answers

0
votes

Could you achieve this using LastNonEmpty?