Forum Discussion

Ktrain's avatar
Ktrain
Frequent Visitor
8 years ago
Solved

Calculate Dynamic Velocity based on the slicer selected

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

Need Help
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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

6 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    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's avatar
      Ktrain
      Frequent Visitor
      8 years ago

      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's avatar
        Ktrain
        Frequent Visitor
        8 years ago

        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.