topic Re: Firstdate producing a results with more than one date for a customer in DAX Commands and Tips

Firstdate producing a results with more than one date for a customer

Tendai — Sat, 14 Sep 2019 06:45:49 GMT

Hi, may you kindly help me.

My firstdate function is not producing my intended result.I want to see the first date the customer started to pay his monthly premiums and the amount the customer paid. But if I use firstdate function it's giving me all the dates that the payment was made and in so doing it's adding up all the amount paid.

This is the syntax I'm using

First Payment Date = FIRSTDATE('macroinsure col_trans_log'[tran_date])

So that tran_amount is an addition of all the transaction made by that client but what i require is transaction amount on that specified first date.Which is 90 see picture below

Re: Firstdate producing a results with more than one date for a customer

MattAllington — Sat, 14 Sep 2019 07:00:58 GMT

You need to write and measure that will extract the transaction amount for the first date. Something like this

First Payment Amt = Calculate(sum(table[amount]),FIRSTDATE('macroinsure col_trans_log'[tran_date]))

Re: Firstdate producing a results with more than one date for a customer

Tendai — Sat, 14 Sep 2019 08:14:37 GMT

Hi, thanks for the quick response. Tried it but it's still giving me the same value it's adding all payments made by the customers

If i put the actual date of transaction im geting this, yet i only want that 90 which was on a monday 1 october 2018

Re: Firstdate producing a results with more than one date for a customer

MattAllington — Sat, 14 Sep 2019 11:21:13 GMT

This is actually made more complex because you are using a single table. I think this is the issue. Try this (I haven’t tested as I am not at my pc).

First Payment Amt = Calculate(sum(table[amount]),all(‘macroinsure col_trans_log'),FIRSTDATE('macroinsure col_trans_log'[tran_date]),values(‘macroinsure col_trans_log'[Policy_No]))

Re: Firstdate producing a results with more than one date for a customer

Tendai — Mon, 16 Sep 2019 11:04:29 GMT

I think let me explain what I'm trying to achieve.

I have a table for Funeral policies. The table contains Policy_number, Customer_name, monthly premium and Agent name who sold the policy. The Agent is paid 100% of the first amount paid by the customer as commission and he only gets the first payment only. So I need to come up with an automated report with first payment date as a slicer, for which if i click august it only gives first payments for august and total commission for the Agent for all the policies he sold.

Policy Number	customer name	Premium Amount	Transaction Date	Agent Name
pol1	Tendai Mavhunga	97	12/2/2018	Andrew
pol343	Charles Jones	120	2/23/2019	Clara
pol346	HLABALINGENE	510	5/16/2018	Andrew
pol345	ISSA	97	7/22/2019	Clara
pol346	HLABALINGENE	510	8/23/2018	John
pol347	MUGABE	89	9/16/2019	John
pol343	Charles Jones	69	9/16/2019	Clara

So I used the Firstdate(Transaction Date) to get the first payment for the customer.

Then for agent commission, I used SUM(Premium Amount)

So I was hoping that if I select August on first payment date it will only give first payments in August however it's adding all payments.

Re: Firstdate producing a results with more than one date for a customer

MattAllington — Mon, 16 Sep 2019 22:08:17 GMT

DAX is more complicated than Excel in that the formulas depend on the data model (table structure, relationships etc). I suggest you produce a sample model with non-sensitive data that you can share. Someone will then help you.