Loop through table and create rows

sec12tst8r84,

In Query Editor of Power BI Desktop, add a custom column using the following formula. Then expand  the custom column  to new rows and change  the type of Custom column  to Date.

if [ST]=null then {Number.From([Date])} else { Number.From([ST])..Number.From([EN]) }

In Report view of Power BI Desktop, right click your table and choose “New Column” to create the following columns.

Month = MONTH(Table[Custom])

FirstDay Of each month = CALCULATE(MIN(Table[Custom]),ALLEXCEPT(Table,Table[Month]))

days = DATEDIFF(Table[ST],Table[EN],DAY)

Previous = CALCULATE(FIRSTNONBLANK(Table[FirstDay Of each month],Table[FirstDay Of each month]),FILTER(Table,Table[Date]=EARLIER(Table[Date])&&Table[FirstDay Of each month]>EARLIER(Table[FirstDay Of each month])))

Column = IF(Table[Previous]=BLANK(),Table[EN],Table[Previous])

Newdays = IF(Table[Previous]=BLANK(),DATEDIFF(Table[FirstDay Of each month],Table[Column],DAY),DATEDIFF(Table[FirstDay Of each month],Table[Previous],DAY))

NewAmount = IF(Table[days]=BLANK(),Table[Amount],(Table[Newdays]/Table[days])*Table[Amount])

Regards,
Lydia