topic Re: Backlog from Excel into DAX/Power BI in DAX Commands and Tips

Backlog from Excel into DAX/Power BI

Anonymous — Sun, 17 Jul 2022 23:58:02 GMT

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!

Re: Backlog from Excel into DAX/Power BI

amitchandak — Mon, 18 Jul 2022 03:39:07 GMT

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.

Re: Backlog from Excel into DAX/Power BI

Anonymous — Mon, 18 Jul 2022 21:12:53 GMT

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])))