cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tendai
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

 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.

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

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.

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.

 

 

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors