powerbi - Power Bi forecast projected sales accross days / months between a date range

Question

I am trying to create a monthly forecast for a list of clients that have different start and end dates for their spending.

I am able to create a formula that would work in excel, but am learning Power Bi and having troubles converting the formula to something that would work inside Power Bi without adding the formula to excel file. Column A-F are how the raw data is delivered. Columns G-R are columns that i am trying to avoid adding thru a Dax formula

Pending Sample excel data

question from:https://stackoverflow.com/questions/65838934/power-bi-forecast-projected-sales-accross-days-months-between-a-date-range

Answer 1

it might help if you unpivot the prorate monthly amounts in power query first (since narrow tables better than wide tables) client Start Date End Date PendingMarket$ PendingShare Month Value Client 1 3/1/2021 3/1/2021 $10,618 3.0% jan 0 Client 1 3/1/2021 3/1/2021 $10,618 3.0% feb 0 Client 1 3/1/2021 3/1/2021 $10,618 3.0% mar 319

then the dax formulae is easy

for a calculated column on the table: Pending Station $ (Prorated) = CALCULATE(SUM(MyTableName[Value]), [Strat Date] = Earlier([Start Date], [End Date] = Earlier[End Date], [Client] = Earlier[Client])

PN earlier get the value of current row for column value