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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

    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

  • 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