Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Backlog from Excel into DAX/Power BI

Hey,

Hopeing to convert this Data from how it is stored into DAX/Power BI.

I can't work out how to get the backlog information to work, Here is the excel formula.
This is for the Monday
=IFERROR(IF(IF(Date!B42<=TODAY()+3,AD41-G41-H41-F41+S41,"")>0,IF(Date!B42<=TODAY()+3,AD41-G41-H41-F41+S41,""),0),"")

 


The information is now stored like this in Power BI,  Columns being Date, Received, Processed and Backlog (the numbers imported from the excel table.)

 

 

Thanks in advance!

Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , You have to take approach of onhand inventory to build cumulative and running total example

     

    Inventory / OnHand
    [Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

    Inventory / OnHand
    CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hey,

    Unfortunately that doesn't look to work.

    Currently this is what the data is outputting.

    The old backlog is what I have manually entered from the Excel data, So Old Backlog is the target outcome i need.
    The Recevied, Processed are just values as shown.
    So need to work out how to get the Total Backlog to line up with Old Backlog.

     

    Total Backlog is using the measure.

    Backlog M =
     
     CALCULATE(SUM('Returns_Inboud/Outbound'[Total Units Processed]),filter('Returns_Inboud/Outbound','Returns_Inboud/Outbound'[Title1] <=maxx('Returns_Inboud/Outbound','Returns_Inboud/Outbound'[Title1]))) - CALCULATE(SUM('Returns_Inboud/Outbound'[Total Returns Received]),filter('Returns_Inboud/Outbound','Returns_Inboud/Outbound'[Title1] <=maxx('Returns_Inboud/Outbound','Returns_Inboud/Outbound'[Title1])))