Forum Discussion

lisampederson's avatar
lisampederson
Helper I
7 years ago
Solved

Forecasting project by day

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...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

     

    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's avatar
lisampederson
Helper I
7 years ago

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

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

     

    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