Inventory tracking from month to month using DAX

[hales231]

Forgive me since I just learned Power BI exists.... I have a "inventory" table containing the starting 'Inventory', 'Brood_Year' ,and 'Raceway_PRAS_Unit' . I have a separate table, "Mortality" that also has 'Brood_Year', 'Raceway_PRAS_Unit' and cumulative monthly mortality in a column called 'Mortality_Count'. I need to take the starting Inventory from the inventory table and subtract the mortalities for each month based on brood year, and raceway.

I'm trying to get a DAX code to produce the EOM_Inventory like in the example below.

I appreciate any help I receive on this


Excel Example:

Tables:
Inventory

Mortality

 

[Matt Allington]

To start, you should understand the way you need to structure your data. Read about the structure in this article I wrote, here The Optimal Shape for Power BI Data

You need 2 dimension tables that don’t currently exist. You need to make them. One is a calendar table that contains a minimum of all end of month dates and a column for year. The second is a rearing unit table containing at least all unique rearing units. Load these, plus the 2 fact tables. Then, if you’re using power BI, simply join the 2 dimension tables to the 2 fact tables.

Then use the columns from the 2 dimension tables on rows and columns in a matrix/pivot table, and use the numeric formulas in the values section. You can write simple DAX like sum(table[column]) to get monthly totals. Running totals are harder and will need something like Calculate(sum(table[column]),All(calendar),calendar[date]<=max(calendar[date]))

Get started and see how you go

 

[hales231]

To start, you should understand the way you need to structure your data. Read about the structure in this article I wrote, here The Optimal Shape for Power BI Data

You need 2 dimension tables that don’t currently exist. You need to make them. One is a calendar table that contains a minimum of all end of month dates and a column for year. The second is a rearing unit table containing at least all unique rearing units. Load these, plus the 2 fact tables. Then, if you’re using power BI, simply join the 2 dimension tables to the 2 fact tables.

Then use the columns from the 2 dimension tables on rows and columns in a matrix/pivot table, and use the numeric formulas in the values section. You can write simple DAX like sum(table[column]) to get monthly totals. Running totals are harder and will need something like Calculate(sum(table[column]),All(calendar),calendar[date]<=max(calendar[date]))

Get started and see how you go

Thank you for the great article, it is very informative (I should have mentioned I also have a basic date table). I've made a 2 dimension table for the rearing unit and have my relationships set like the picture below.

I've been able to follow a video to get DAX for calculating cumulative mortalities from the end of one month to the next using the syntax below:

Cumulative_Mort = var CurrentDate = Mortality[Date]
var CurrentBrood_Year = Mortality[Brood_Year]
var CurrentRaceway_PRAS_Unit = Mortality[Raceway_PRAS_Unit]
var FilteredTable = FILTER(Mortality,Mortality[Date]<= CurrentDate
&& Mortality[Brood_Year] = CurrentBrood_Year
&& Mortality[Raceway_PRAS_Unit] = CurrentRaceway_PRAS_Unit)
RETURN
CALCULATE(SUM(Mortality[Mortality_Count]),FilteredTable)

Results from the above code:

But I'm quite stuck on how to get to my End of Month Inventory for each Brood Year/ Raceway combo.

Again thank you for any assistance with this.

 

[Matt Allington]

You have written the Cum Mort as a calculated column. This is suboptimum. Read why here Calculated Columns vs Measures in DAX

You should write this as a measure. Bring date from your calendar table into a matrix in PBI (or Pivot in Excel) and have another go. Your general approach to the formula is correct but it will need to be modified. You can't refer to columns in a measure unless they are wrapped in a function. eg, you can't write
var CurrentDate = Mortality[Date]
there are 2 issues. You should (almost) always refer to the dimension table (Date in this case) plus you need to wrap the column in a function. MAX does the job in this case

var CurrentDate = MAX(Date[Date])

 

[hales231]

You have written the Cum Mort as a calculated column. This is suboptimum. Read why here Calculated Columns vs Measures in DAX

You should write this as a measure. Bring date from your calendar table into a matrix in PBI (or Pivot in Excel) and have another go. Your general approach to the formula is correct but it will need to be modified. You can't refer to columns in a measure unless they are wrapped in a function. eg, you can't write
var CurrentDate = Mortality[Date]
there are 2 issues. You should (almost) always refer to the dimension table (Date in this case) plus you need to wrap the column in a function. MAX does the job in this case

var CurrentDate = MAX(Date[Date])

Matt,

Thank you again for the very useful article. I've updated to make this a measure but I'm sure it's sloppy (I've been having a tough time figuring out what function to use).

Cumulative_Mort =
VAR CurrentDate = MAX('Date'[Date])
VAR CurrentBrood_Year = SELECTEDVALUE(Mortality[Brood_Year])
VAR CurrentRaceway_PRAS_Unit = SELECTEDVALUE(Mortality[Raceway_PRAS_Unit])
VAR CurrentRearingUnit= SELECTEDVALUE('Mortality'[Rearing Unit])
VAR FilteredTable =
FILTER(
ALL(Mortality),
Mortality[Date] <= CurrentDate &&
Mortality[Brood_Year] = CurrentBrood_Year &&
Mortality[Raceway_PRAS_Unit] = CurrentRaceway_PRAS_Unit &&
Mortality[Rearing Unit] = CurrentRearingUnit
)
RETURN
CALCULATE(
SUM(Mortality[Mortality_Count]),
FilteredTable
)


Given this, can I take my starting inventory and simply subtract the mortality for each month?

 

[Matt Allington]

It's hard for me to say without seeing it. I encourage you to build a visual and test it. I find creating a matrix the best. Put dates on the rows of the matrix and add your measure. Does it look right? Test it to make sure. Now put your starting inventory into the matrix. Does it look right? My guess is you will have a starting inventory only for the first period of time and nothing for the subsequent periods. This is because the calendar filters the data; that's what it is designed to do. So you may need to write a starting inventory measure; something that removes the filter for the date. You need a measure that will return the correct starting inventory regardless of the dates on the rows in the matrix. Maybe something like CALCULATE(sum(column),ALL(Calendar)) but it does depend on the data. Solve this problem first, then you can subtract this from your cumulated mortality.