Average Value based on Min and Max of a date slicer

[NBOnecall]

Hi,

I have a data set that looks like below. I have Inventory value for each day inventory is available. If inventory is not available it does not populate the data set. For example there is no data for 2/7/19, 2/8/19 or 2/19/19. I would like to calculate the Average Value Based on a slicer deteremined by the min and max date choosen. It takes that value at the min and max adds them together and divides by 2. Needs to be calculated based of InternalID and LocationID.

The one issue that I can see is if I choose a date without a value there that it defaults to 0 for that value. See expected results below.

Thank you for taking a look!

[Cmcmahan · Accepted solution]

Ahh, I think I was trying to be too clever, using an AVERAGEA expression. The problem is that the filter is catching the date with blank data before it even gets to the AVERAGEA part, so it isn't counted in the average.  You could get around this by using LEFTJOINS instead of straight filters, but that sounds like a headache.

Instead, you can set this up as your own manual average:

AvgValue = 
VAR avg = CALCULATE( SUM(DataTable[Value])/2, FILTER(DataTable, DataTable[Inventory Date] = MAX(DateTable[Date]) || MIN(DateTable[Date]) ))
RETURN
IF(avg = 0, BLANK(), avg)

[NBOnecall]

Anyone have any ideas on how to fix this?

[Cmcmahan]

So it looks like in your list of expected outcomes, you WANT to treat blanks in your data as zeroes.  You don't want to use the values in between, you just want to take the mathematical average of the value on MIN([InventoryDate]) and MAX([InventoryDate]).  If there's a blank on one of the days, treat as a zero, but if there's a blank on both days, return a blank instead.

Your measure would look like this:

AvgValue = 
VAR avg = CALCULATE( AVERAGEA(DataTable[Value]), FILTER(DataTable, DataTable[Inventory Date] = MAX(DateTable[Date]) || MIN(DateTable[Date]) ))
RETURN
IF(avg = 0, BLANK(), avg)

It's important here that your slicer be running off a date dimension that's related to your data, because MAX(DataTable[Inventory Date]) will just give the max available date that's allowed by the slicer.  A date dimension will have the blank dates available.

[Cmcmahan · Accepted solution]

Ahh, I think I was trying to be too clever, using an AVERAGEA expression. The problem is that the filter is catching the date with blank data before it even gets to the AVERAGEA part, so it isn't counted in the average.  You could get around this by using LEFTJOINS instead of straight filters, but that sounds like a headache.

Instead, you can set this up as your own manual average:

AvgValue = 
VAR avg = CALCULATE( SUM(DataTable[Value])/2, FILTER(DataTable, DataTable[Inventory Date] = MAX(DateTable[Date]) || MIN(DateTable[Date]) ))
RETURN
IF(avg = 0, BLANK(), avg)

[NBOnecall · Accepted solution]

After your help and some more searching on the forum I ended up going with the following lines of code.

MinValue = 
VAR Min1 = CALCULATE( SUM('ns InventorySnapshot'[Value]), FILTER('ns InventorySnapshot', 'ns InventorySnapshot'[InventoryDate] = MIN(Dimtime[Date]) ))
return
IF(ISBLANK(Min1), 0, Min1)

MaxValue = 
VAR Max1 = CALCULATE( SUM('ns InventorySnapshot'[Value]), FILTER('ns InventorySnapshot', 'ns InventorySnapshot'[InventoryDate] = MAX(Dimtime[Date]) ))
return
IF(ISBLANK(max1), 0, Max1)

AvgValue2 = ([MinValue]+[MaxValue])/2

Yes it is a couple more measures, but it gets my desired results.

[Cmcmahan]

Yup! That's exactly what the second version is doing, except in one step instead of as multiple measures.  Always multiple ways to skin the cat in Power BI. Glad you got it figured out!

[NBOnecall]

Thank you, for the most part this is exactly what I need, but it is having issues with dates no in the data set that need to be 0, and it might have been from not having a little bit more data in the original data. To start here is the relationship of the dimtime table.

Second is the data for this specific item, I am basing this on the hand number for the value. So if nothing is OnHand, but on order a line will be available, but it would be a $0 value. I still have cases where there is no data at all for the dates.

Here is the result of going between Feb 26th ($598.77) and the 28th ($0). It should be $299.385. Any thoughts?

Thanks!