Forum Discussion

NBOnecall's avatar
NBOnecall
Helper V
7 years ago
Solved

Average based on date slicer from DImtime table

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 t...
Need Help
  • NBOnecall's avatar
    NBOnecall
    7 years ago

    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's avatar
v-cherch-msft
Microsoft Employee
7 years ago

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's avatar
NBOnecall
Helper V
7 years ago

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's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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,