cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tendai Frequent Visitor
Frequent Visitor

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

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])
image.png
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
Capture.JPG

 

 

5 REPLIES 5
Highlighted

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

 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]))



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Tendai Frequent Visitor
Frequent Visitor

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

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

Capture.JPG

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

Capture.JPG

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

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]))



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Tendai Frequent Visitor
Frequent Visitor

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

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 AmountTransaction DateAgent Name
pol1Tendai Mavhunga9712/2/2018Andrew
pol343Charles Jones1202/23/2019Clara
pol346HLABALINGENE5105/16/2018Andrew
pol345ISSA977/22/2019Clara
pol346HLABALINGENE5108/23/2018John
pol347MUGABE899/16/2019John
pol343Charles Jones699/16/2019Clara

 

 

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

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.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors