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

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.

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

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

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

        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.