Forecasting project by day

[lisampederson]

Hello,

I would like to use PowerBI to help aggregate forecasts per project so the dashboard graphics can show summaries.  I have an estimate, project ID, and due date.  I can't seem to figure out how to get PowerBI to do some of the calculations and combine data without making a spreadsheet per project.  Any help would be appreciated!  Thank you!

Individual Project Logic:

- goes through date column until finds match to start date

- Model to enter daily cost for Phase 1 until hit duration of Phase 1, then

- Model to enter daily cost for Phase 2 until hit duration of Phase 2, then

- Model to enter daily cost for Phase 3 until hit duration of Phase 3, then

- ends at due date

- end

Model Logic:

- sums all daily project cost per project to get overall cost per day

Dashboard:

- uses overall cost per day (rolled up by year)

Data File:

https://drive.google.com/file/d/1binQLGBUq2Sx8oCR5JKW6aXAXsQzEEaS/view?usp=sharing 

[Anonymous]

HI @lisampederson,

You can take a look at following sample about your requirement, I transform your calculator table at query editor side and create a dax expand table with detailed day of date range to create visuals.

Regards,

Xiaoxin Sheng

[lisampederson]

Hello,  It looks like my response disappeared...

Thank you @Anonymous but it doesn't seem like my problem was solved.

1.  The "Daily Cost" should add up to the "estimate" for the "Project ID."  I noticed a small error in my formula, so I've made corrections to the excel file.  This is not the cause of the error though.  Is the Daily Cost / Total Attribute calculating correctly?

2.  The relationships are not connecting amoung the sources, so I'm unable to view graphics and tables.  Do you know how to fix this?

I believe you figured out the date piece that I was stuck on, but I'm unable to tell since the numbers aren't totalling correctly.

Thank you!

Lisa

[lisampederson]

Updated document:

https://docs.google.com/spreadsheets/d/1czqbCytHOCI7RmojQL1gjK4KXwNRkviV6mEsw-JhRlI/edit?usp=sharing

[Anonymous · Accepted solution]

HI @lisampederson,

#1. It seems like I forget to calculate rolling total, I modify my measure to change conditions, now it can be summarized based current year.

Total Attribute = 
var currDate=MAX('Expand Table'[Detail Date])
return
CALCULATE (
    SUM ( 'Expand Table'[Daily cost] ),
    FILTER (
        ALLSELECTED ( 'Expand Table' ),
        [Detail Date] <= MAX ( [Detail Date] )
            && YEAR ( [Detail Date] ) = YEAR (currDate ) 
    ),
    VALUES ( 'Calculator'[Unique Key] )
)

#2. Since I used original table to generate expand table, so if you direct create relationship between these table columns, it will return a 'circular dependency' error. In my opinion, you can try to create a bridge table with relationship key to link these tables.

Notice: Unique key is merged with project id and attribute column.

Regards,
Xiaoxin Sheng

[lisampederson]

Thank you for helping, but this solution does not seem to work.  @Anonymous

1. The "Total Attribute" measure you created to solve the problem does not add up to the estimate, which indicates there is an error somewhere.  The "Daily Cost" by phase ("Attribute") should total to the "estimate" for the "Project ID".  I made an error in one of the phase cost columns in the Calculator tab, which I've edited in the excel.  However, this is not the source of the error.

https://docs.google.com/spreadsheets/d/1czqbCytHOCI7RmojQL1gjK4KXwNRkviV6mEsw-JhRlI/edit?usp=sharing 

2. PowerBI is also unable to recognize relationships between the sources.  Do you know how to solve this?  The graphs are not working.

It looks like you did get the date problem I was having solved so hopefully it will be correct once the errors above are fixed!!

Thank you!
Lisa