Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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]))
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
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]))
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
42 | |
39 | |
19 | |
19 |