Analysing transactions in the same period

Question

Hi,

for a my solution sample, I need to analyse the account transactions in the same period, that I could select.

I've the Fact_AccountTransactions with ID, Customer_ID, City_ID, TransactionAmount, TransactionDate. I could have more transactions for the same customer in the same day. For semplicity, my analysis period is the day. I'd like to avoid to manage also the time and so I think to use the ID of the fact table to distinguish the transactions in the same day.

I've also the dimension tables: Customers, with Customer_ID and Name_Surname; BankAgencies, with Agency_Id, Description, City_Id; Time with Datekey, year, month, day.

I need to calculate the last transaction amount in the same day for distinct customers, without any dependencies of city_id. As the first step, I've tried this formula: calculate(MAX(Fact_AccountTransactions[ID]);DISTINCT(Fact_AccountTransactions[Customer_ID])) to obtain the last transaction but it functions if I don't put the city _id ad a row label in the pivot table. When I consider the city_id as a row label I obtain more of one transaction ID in the same day, one for different cities.

Moreover, I need to count the last transaction in the same day for distinct customers.

Any suggests to me, please? Many thanks

Answer 1

you may try something like this:

Test2:=SUMX(VALUES(FactFinance[OrganizationKey]); LASTNONBLANK(FactFinance[Amount]; SUM(FactFinance[Amount])))

where [OrganizationKey] would be your Customer_ID
sum up the Last Amount for each customer in the current context

hth,
gerhard

---

- www.pmOne.com -

Answer 2

Hi Gerhard, thanks for your reply.

But also I need to manage the indipendence respect to the city_id by ALL(Fact_AccountTransactions[city_id]).

Thanks