cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
J_R_Cribb
Helper I
Helper I

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 stock level and is therefore not associated with a date.

 

After building a matrix to display products in the rows and date/weeks in columns, I have attempted to create a calculated measure to show the running total combining current stock level and incoming deliveries. The measure is a simple calculate expression, and the outcome is correct, however, the output values only appear in my matrix under the week columns where there are incoming deliveries. 

 

Is there a way that I can show the stock level under all weeks, and not just those with incoming deliveries?

 

Thank you in advance!

1 ACCEPTED SOLUTION

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

Can't show values under all Date Columns_1.jpg

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

Can't show values under all Date Columns_2.jpg

 

Best Regards,

Herbert

View solution in original post

7 REPLIES 7
v-haibl-msft
Microsoft
Microsoft

@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

@v-haibl-msft

 

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

 

 


Stock.PNGIncoming.PNG

 

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:

 

Running Total.PNG

 

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!

 

 

 

 

 

 

 

 

@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

@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

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

Can't show values under all Date Columns_1.jpg

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

Can't show values under all Date Columns_2.jpg

 

Best Regards,

Herbert

View solution in original post

@v-haibl-msft

 

Please could you provide an explanation on how your formulae for LastNoBlankWeek & LastNoBlankTotal operate?


Thank you, 

 

J

 

@v-haibl-msft

 

Hi Herbert, 

 

Thank you for detailing this perfectly - I've tried and tested for all products that I have data for (100+) and it gives me a running total which I couldn't achieve before.

 

If I filter from a certain date, say Week 33 for instance, would it only take into account future data points?

 

I also have a third table giving descriptions for each product code, which is linked to both data tables. Whenever I have tried to include the description in the table it either crashes or just does not work - is this perhaps a memory issue due to the vast number of calculations that BI needs to do?

 

Thank you again, 

 

J

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!