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 all,
I have a data list that contains the following fields:
Amount | Application | Installment frequency | Installments start date | Installments end date |
$400.00 | Recurring gift | Yearly | 25/10/2020 | 23/09/2023 |
$27.78 | Recurring gift | Monthly | 25/10/2020 | 25/09/2023 |
$30.00 | Recurring gift | Monthly | 25/10/2020 | 25/09/2023 |
$2,000.00 | Recurring gift | Yearly | 25/10/2020 | 25/09/2022 |
$55.55 | Recurring gift | Monthly | 25/10/2020 | 25/09/2023 |
$10.00 | Recurring gift | Monthly | 25/10/2020 | |
$20.00 | Recurring gift | Monthly | 25/10/2020 | 25/09/2022 |
$150.00 | Recurring gift | Yearly | 25/10/2020 | |
$30.00 | Recurring gift | Monthly | 25/10/2020 | 25/09/2023 |
$15.00 | Recurring gift | Monthly | 25/10/2020 |
Is it possible to create a calculated column that reflects the number of installment involved for each of these gifts (either to the end-date or up to 5 years where there is no end-date or the end-date is more than 5 years)?
Ultimately I am trying to use the number of instalment to calculate the total amount of gift committed.
For example:
- a monthly gift of $30 for three years would look like this = $30 x 36 = $1080
- an annual gift of $300 for three years would look like this = $300 x 3 = $900
Any help will be greatly appreaciated. Thank you.
Diana
Solved! Go to Solution.
Hi,
You can try this:
New Column =
VAR CountMonthlyInstallments =
DATEDIFF (
'Table'[Installments start date],
'Table'[Installments end date],
MONTH
) + 1
VAR CountYearlyInstallments =
DATEDIFF (
'Table'[Installments start date],
'Table'[Installments end date],
YEAR
)
RETURN
IF (
NOT ( ISBLANK ( 'Table'[Installments end date] ) ),
SWITCH (
'Table'[Installment frequency],
"Monthly", CountMonthlyInstallments * 'Table'[Amount],
"Yearly", CountYearlyInstallments * 'Table'[Amount]
)
)
HI @DianaT,
It sounds like a common data analysis requirement across the date range from multiple date fields. You can take a look at the following blog 'start date', 'end date' part if it meets your requirement:
In addition, you can also try to create a new table to expand the date range then you can simply calculate the values based on the new table date fields:
Spread revenue across period based on start and end date, slicer and dase this using different dates
Regards,
Xiaoxin Sheng
Thank you Xiaoxin. These references are very useful.
@IreneNordOne, this is really great and very close to what I'm looking for!
At the last part of the DAX, I tried to incorporate an extra condition to specify what happened when end date is blank, like this:
IF ( ISBLANK ( 'Table'[Installments end date] ) ),
SWITCH (
'Table'[Installment frequency],
"Monthly", 60 * 'Table'[Amount],
"Yearly", 5 * 'Table'[Amount]
)
)
Is this do-able? I've been learning DAX for a good couple months now and find incoporating multiple conditions or filters in one DAX very tricky...
Thank you so much for your help.
Cheers,
Diana
Hi,
You can try this:
New Column =
VAR CountMonthlyInstallments =
DATEDIFF (
'Table'[Installments start date],
'Table'[Installments end date],
MONTH
) + 1
VAR CountYearlyInstallments =
DATEDIFF (
'Table'[Installments start date],
'Table'[Installments end date],
YEAR
)
RETURN
IF (
NOT ( ISBLANK ( 'Table'[Installments end date] ) ),
SWITCH (
'Table'[Installment frequency],
"Monthly", CountMonthlyInstallments * 'Table'[Amount],
"Yearly", CountYearlyInstallments * 'Table'[Amount]
)
)
@DianaT , if need to split the data month-wise, refer to the file attached. I have done it from date range to the day, you have to do only once in a month. so add filter [date] =eomonth([date],0)
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |