Forum Discussion

J_R_Cribb's avatar
J_R_Cribb
Icon for Helper I rankHelper I
10 years ago
Solved

Can't show values under all Date Columns?! Help Please

I am trying to create a running stock total for a number of products over time. I am using two data values (qty), the first is associated with a date (Incoming deliveries), the second is a current st...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    J_R_Cribb

     

    If there are no incoming deliveries, the product data will not exist for that week in your dataset, right?

    In following test dataset, there is no incoming deliveries for “-62901” on 3/14, 3/28, 3/29 and 4/4.

    We can first create a new table with following formula. Relate this new table with stock table using product.

    FullTable = 
GENERATE (
    SUMMARIZECOLUMNS ( Table2[Product] ),
    SUMMARIZECOLUMNS ( Table2[Date] )
)

    Then create WeekNum column and Quantity column in this new table.

    WeekNum =
WEEKNUM ( FullTable[Date] )
    Quantity = 
CALCULATE (
    SUM ( Table2[Quantity] ),
    FILTER (
        Table2,
        Table2[Date] = FullTable[Date]
            && Table2[Product] = FullTable[Product]
    )
)

    At last, create a measure with following formula. I’ve also upload my .pbix file here for reference.

    LastNoBlankTotal = 
VAR LastNoBlankWeek =
    CALCULATE (
        MAX ( FullTable[WeekNum] ),
        FILTER (
            ALL ( FullTable ),
            FullTable[WeekNum] < MAX ( FullTable[WeekNum] )
                && FullTable[Quantity] <> BLANK ()
        ),
        VALUES ( Table1[stp_product] )
    )
RETURN
    (
        IF (
            [Incoming Qty] = BLANK (),
            [Stock Qty]
                + CALCULATE ( [Incoming Qty], FullTable[WeekNum] = LastNoBlankWeek ),
            [Rolling Total]
        )
)

     

    Best Regards,

    Herbert

v-haibl-msft's avatar
v-haibl-msft
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

J_R_Cribb

 

Could you please provide a simple data sample and the calculated measure you created to us? So that we can know more clearly about you problem.

 

Best Regards,

Herbert

J_R_Cribb's avatar
J_R_Cribb
Icon for Helper I rankHelper I
10 years ago

v-haibl-msft

 

Thank you for your response. I have the two sets below (simplified).

 

 


 

I have created a calculated column to withdraw the week number from the bottom table (incoming), and there is a relationship between the incoming table and stock table.

 

I am laying out as below:

 

 

As you can see, I have my product as rows, week number as columns. I have created measures for the Incoming Qty and Stock Qty, and the simple calculation I have used is: Rolling Total = [Stock Qty] + [Incoming Qty].

 

I'd like to see the blank weeks filled in, but I can't seem to achieve it having tried various calculations and seeking other advice on the forum.

 

Thank you!

 

 

 

 

 

 

 

 

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    J_R_Cribb

     

    So you want to fill the blank with value of previous week? For example, the value of ‘product -61993’ and ‘week number 36’ should be -30?

    BTW, what is the formula of your measures for the Incoming Qty and Stock Qty?

     

    Best Regards,

    Herbert

    • J_R_Cribb's avatar
      J_R_Cribb
      Icon for Helper I rankHelper I
      10 years ago

      v-haibl-msft

       

      Yes that's exactly what I would like to do!

       

      My measures are just a SUM of the original value - I don't know whether this was necessary but in my mind made them easier to work with.

       

      Thank you Herbert, 

       

      J

      • v-haibl-msft's avatar
        v-haibl-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        10 years ago

        J_R_Cribb

         

        If there are no incoming deliveries, the product data will not exist for that week in your dataset, right?

        In following test dataset, there is no incoming deliveries for “-62901” on 3/14, 3/28, 3/29 and 4/4.

        We can first create a new table with following formula. Relate this new table with stock table using product.

        FullTable = 
GENERATE (
    SUMMARIZECOLUMNS ( Table2[Product] ),
    SUMMARIZECOLUMNS ( Table2[Date] )
)

        Then create WeekNum column and Quantity column in this new table.

        WeekNum =
WEEKNUM ( FullTable[Date] )
        Quantity = 
CALCULATE (
    SUM ( Table2[Quantity] ),
    FILTER (
        Table2,
        Table2[Date] = FullTable[Date]
            && Table2[Product] = FullTable[Product]
    )
)

        At last, create a measure with following formula. I’ve also upload my .pbix file here for reference.

        LastNoBlankTotal = 
VAR LastNoBlankWeek =
    CALCULATE (
        MAX ( FullTable[WeekNum] ),
        FILTER (
            ALL ( FullTable ),
            FullTable[WeekNum] < MAX ( FullTable[WeekNum] )
                && FullTable[Quantity] <> BLANK ()
        ),
        VALUES ( Table1[stp_product] )
    )
RETURN
    (
        IF (
            [Incoming Qty] = BLANK (),
            [Stock Qty]
                + CALCULATE ( [Incoming Qty], FullTable[WeekNum] = LastNoBlankWeek ),
            [Rolling Total]
        )
)

         

        Best Regards,

        Herbert