locked
Autofill Forecast data from Budget data RRS feed

  • Question

  • Hi,

    Im trying to achieve the following and I just cant crack it!!

    What I want:

    If there is no data in datatable "Forecast" then Excel (power pivot pref) should autofill the data from datatable "Budget".

    Example: If I forecast 100 sold units of iPads, the forecast value should be master, and in the new table it should return the value 100. 

    Example 2: If I dont forecast any sales of iPads, but there is a budget of 50 sold iPads in the budge table, the new table should return the value 50. 

    My setup:

    • 1 datatable "Forecast"
    • 1 datatable "Budget"
    • Both tables have the exact same setup
    • There is a key ID field that is unique in the Budget table. 
    • If I forecast, I first type in the key ID, so it should be possible with some lookup against the Budget table with the key ID?
    • The tables are in separate sheets and then added to the datamodel
    • I have also a data query appending them into a 3rd table that I currently use .

    In very loose terms im trying to do something like; If Forecast table contains key ID that is also in the Budget Table, return the value of Forecast table (100).

    If there is no value in the forecast table, but there is a value in the budget table (50) return the value 50 into the table. 

    Any help would be much appreciated!!

    Thursday, April 14, 2016 1:37 PM

Answers

All replies

  • Hi Tonijj,

    According to your description, you need auto fill the data from data table "Budget" if there is no data in data table "Forecast", right?

    If that is the case, you can use ISBLANK function to check if the column has data or not, if there is no data in that column, use LOOKUPVALUE to get value for Budget table. I have tested it on my local environment, here is that sample DAX expression for you reference.
    =IF(ISBLANK(Forecast[Forecast]),LOOKUPVALUE(Budget[Budget],Budget[ID],Forecast[ID]),Forecast[Forecast])

    Reference
    https://msdn.microsoft.com/en-us/library/gg492170.aspx?f=255&MSPPError=-2147217396
    https://msdn.microsoft.com/en-us/library/ee634204.aspx

    Regards,


    Charlie Liao
    TechNet Community Support

    Sunday, April 24, 2016 6:50 AM
  • Hi Charlie,

    Thanks for your response!! 

    The thing is that both the Budget and Forecast data are in the same table, I have done a query so that both the Budget and Forecast tabs are automatically updated and joint into table 3. Table 3 contains both data. 

    The variable "Budget" and "Forecast" are in the same column.

    So unfortunately it wont bite :( 

    Any ideas of how one could modify your formula a bit?

    Thanks again for the help, appreciate it greatly!!

    Friday, May 13, 2016 7:09 AM