Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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 Employee
Microsoft Employee

@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

@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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.