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.
Dear Power BI Community,
I am hoping for your help with the following problem.
I have data from a Gymnasium that also offers training courses for members:
GymTrainingTable
CustomerID | CourseID | Training | StartDate | EndDate | Value |
CID4321 | GB101 | Gym Basics 101 | 1/01/2020 | 1/09/2020 | $10,000 |
CID4321 | IP102 | Intermediate Power Lifting | 1/01/2021 | 1/12/2022 | $14,000 |
CID4325 | GT000 | Gym Trial | 1/05/2020 | 1/09/2020 | $2,000 |
CID4435 | IP102 | Intermediate Power Lifting | 1/01/2020 | 1/12/2021 | $14,000 |
CID4438 | GB101 | Gym Basics 101 | 1/01/2020 | 1/09/2020 | $10,000 |
CID4438 | HI102 | HIIT Training | 1/01/2021 | 1/06/2021 | $5,000 |
CID5553 | HI102 | HIIT Training | 1/01/2020 | 1/06/2020 | $5,000 |
CID5651 | GB101 | Gym Basics 101 | 1/01/2021 | 1/09/2021 | $10,000 |
CID5652 | IP102 | Intermediate Power Lifting | 1/01/2021 | 1/12/2022 | $14,000 |
CID4356 | GT000 | Gym Trial | 1/05/2021 | 1/05/2022 | $6,000 |
As above, each Customer enrols into a specific course of training, with a start and end date. the Unique ID for each row is the combination of Customer ID and CourseID, which can be referred to as StudyPlanID
I need to work out the installment revenue for each half year period. What I would like to do is create a new installment table to separate the GymTraingingTable into an installments table, which will show projected revenue.
-For those StudyPlanIDs where the course will exceed 6 months, (excluding GT000), the total value of their course is separated into installment payments based on the number of 6 month semesters. Eg. IP102 has a cost of $14000 and duration of 24 months, so there will be 4 installments of $3500. A 9 month course for example will involve 2 installments, the first initially and the second after 6 months.
-For those StudyPlanIDs for a course of 6 months or less, (excluding GT000), the total value of their course is due at the beginning of their study. Therefore just a single installment is required.
-For any GT000 study, regardless of course length or cost, payments are due monthly. Eg. 12 months of GT000 will cost $6000 overall, and will require 12 monthly installments of $500.
How can I create a new table which takes data from GymTrainingTable and creates a new row for each installment that includes the following:
CustomerID | CourseID | InstallmentNumber | InstallmentDueDate | Value |
Is this possible in DAX alone or will it require a combination of both DAX and Power Query?
@amitchandak @SpartaBI Please help if you can!
Thanks in advance, Walker
@walker4545 , Try a new Table in DAX
Table = Addcolumns(filter(generate(Data,SELECTCOLUMNS( GENERATESERIES(5,datediff(Min(Data[StartDate]), Max(Data[EndDate]),Month)+1,6) ,"Value1", [Value])) , [Value1] <= datediff([StartDate],[EndDate],MONTH)), "Date", EOMONTH([StartDate],[Value1]))
find the file attached
Dear @amitchandak
Thanks, I think that's a step in the right direction, however I think the new column Value is incorrect, as it still just generates the full amount of the course. Also, for GT000 the price should be broken down into monthly payments, not just one single up front payment.
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |