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

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.

3 Replies

  • 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,