Create New Table and Populate Rows Conditionally (DAX, Power Query)

[walker4545]

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

[amitchandak]

@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 

[walker4545]

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.