Calculate Dynamic Velocity based on the slicer selected

[Ktrain]

Hi Everyone,

I am trying to do calculate the average velocity for each skus base on the month slicer. I am really appriciate for any help. Thank you.

Sample data

[v-juanli-msft · Accepted solution]

Hi @Ktrain

1.Create a calendar date table

Table = CALENDAR(DATE(2018,1,1),DATE(2018,12,31))

then in this table, create columns and measures

columns, then add this column to the sliceron the report.

calendar month = MONTH([Date])

Measures

min month = MIN('Table'[calendar month])

max month = MAX([calendar month])

2.create relationships between two tables

3. create measures in the data table

Assume data table is like this

sum =
CALCULATE (
    SUM ( Sheet6[velocity] ),
    FILTER (
        ALLEXCEPT ( Sheet6, Sheet6[skus] ),
        [month] >= [min month]
            && [month] <= [max month]
    )
)

numbers fo month =
CALCULATE (
    COUNT ( Sheet6[month] ),
    FILTER (
        ALLEXCEPT ( Sheet6, Sheet6[skus] ),
        [month] >= [min month]
            && [month] <= [max month]
    )
)

aveage = [sum]/[numbers fo month]

Best Regards

Maggie

[Ktrain]

Thank you @v-juanli-msft. I will try it out, and let you know. Thank you very much to taking your time to help me. 

[Ktrain]

Hi @v-juanli-msft Maggie,

I tried to created the Date Table, but the data won't change when I change the months. This is what I current have.

I have the data table ( Order Line)  with skus and SHIPPED ITEM. I also created the Skus tables as you can see in the relationship table attchement to connect the item id from inventory details tables to Order Line table ( my data table) due to no unique value between Inventory Details and Order Line Table.

In the Order Line Table, I created 2 column Month and Year, so I used it for slicer. I also follwoing your instuction to create min month ,max month, sum and number of months. However, it is not working for me. Would you please take look and let me know what I did wrong.

Thank you.

 FieldsOrder Line Table ( Data)Sum FormulaTable Relationship.

[v-juanli-msft]

Hi @Ktrain

I can't see "calendar date table" from your screenshot, how do you create the [Min month] or [Max month]?

Best Regards

maggie

[Ktrain]

Hi @v-juanli-msft

I did made the calendar table, but it is not work. I removed it, and make the [Min Month], [Max Month] and Month column in the Order Line Data. 

I created [Min Month] and [Max Month] as mesasure.

Thank you.

[Ktrain]

Hi Maggie @v-juanli-msft,

I found why the calendar is not work due to the receveing date from my data has date and time. I edit to date format only. Now it is working . However, my omputer can't handle the sum calulation, due to large data from. I am still waiting for swapping out new computer will more memory. Hopefuly, I will see result once I have new computer. 

Thank you.