cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
J_R_Cribb Regular Visitor
Regular Visitor

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

Accepted Solutions
v-haibl-msft Super Contributor
Super Contributor

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

@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

7 REPLIES 7
v-haibl-msft Super Contributor
Super Contributor

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

@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 Regular Visitor
Regular Visitor

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

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

 

 

 

 

 

 

 

 

v-haibl-msft Super Contributor
Super Contributor

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

@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 Regular Visitor
Regular Visitor

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

@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 Super Contributor
Super Contributor

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

@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

J_R_Cribb Regular Visitor
Regular Visitor

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

@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

Highlighted
J_R_Cribb Regular Visitor
Regular Visitor

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

@v-haibl-msft

 

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


Thank you, 

 

J

 

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 63 members 1,213 guests
Please welcome our newest community members: