How to create formula-based columns in Power BI for forecasting?

[akumar_nexamp]

Hello,

I have a somewhat unique table sitting in an excel file and I'm unsure how exactly to rewrite it in Power BI. Essentially, I have multiple projects in my rows, a date by which the project's capacity should be filled, and columns showing me months from today in which the project fill rate is evenly distributed by month until the start date of that project. 

Currently, my month columns are based on 'today()' for the first column with 5/12 and then using EDATE to add 1 month and I've manually stretch my columns to the end of 2022. I then have two sets of IF statements to tell me the months that a project is eligible to be "Sell" status and then another similar table to bring in the capacity that needs to be filled in remaining months.

This table is used to forecast the necessary totals by month that need to be filled as time passes forward. I'm guessing the most 'manual' solution would be to hard code column names and merely use them as references. I appreciate any help!

[V-pazhen-msft]

@akumar_nexamp 

You need to turn your data into Power BI with appropriate format first, then think of how to create dax to meet the logic. Based on your screenshot, you need change table a format with 1 column header and data value on the row. For example:

Project	Date	Capacity size	Actual	......
A	2021.2.4	100	25	......
X	2021.2.5	50	15	......

Paul Zheng _ Community Support Team

[akumar_nexamp]

Thank you for the response! Yes, that's what I ended up doing. I created a separate date table and then matched in the corresponding date values and got my sums via SUMX.