Average based on date slicer from DImtime table

[NBOnecall]

Hi,

I have been searching this forum and couldn't find anything relating to my question. Hoping someone can help me out.

I have a table called InventorySnapshot and want to create a measure that finds the average of the column value seen below based on the date range slicer. The date range slicer has a relationship between the inventory date and a calender date.

My expected results would be the following;

Date slicer: Jan 31st - Feb 1st, $0 (Jan 31st Value) + $1,137.40 (Feb 1st Value)/2(days) = 568.70

Date Slicer: Jan 31st - Feb 3rd, ($0 + $1,137.40 + $1,137.40 + $1,137.40)/4 = 853.05

Date Slicer: Feb 1st - Feb 3rd, ($1,137.40 + $1,137.40 + $1,137.40)/3 = 1,137.40

As you can see January 31st is not in the data set, but because of the datedim table, I would still be able to use the slicer to get that date, I just want the value to be 0.

Thanks,

Noel

[v-cherch-msft]

Hi @NBOnecall 

Could you share a simplified sample file here to reproduce your scenario?You can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.Please explain more about your assumed sample data and expected output.

How to Get Your Question Answered Quickly

Regards,

[NBOnecall · Accepted solution]

I think I got this solved. I don't know if it is the perfect way, but at least from my checking it seems to work.

True Average = sum('ns InventorySnapshot'[Value])/[Days]

Days = 
VAR a =
    FIRSTDATE ( Dimtime[Date] )
VAR b =
    CALCULATE ( MAX ( Dimtime[Date] ), ALLSELECTED ( Dimtime[Date] ) )
RETURN
    DATEDIFF ( a, b, DAY )+1

I would think there would be a better way, but I don't know.

[v-cherch-msft]

Hi @NBOnecall 

Glad to hear you've solved it, please accept your reply as solution, that way, other community members will easily find the solution when they get same issue.

Regards,