Show Last Available Value in Timeline Report

[Anonymous]

Hi all, 

Let's say I have inventory table with the following data:

Jan 2017: 10

Feb 2017: 20

Mar 2017: 30

and then I want to create a Date by Inventory Quantity matrix/bar chart that continues to populate the numbers from Apr 2017 onwards with the last available inventory value (i.e. 30, from Mar 2017). The expected output is as follows:

Jan 2017: 10

Feb 2017: 20

Mar 2017: 30

Apr 2017: 30

...

Nov 2017: 30

Dec 2017: 30

How do I write the DAX measure? I've tried the following but it doesn't work.

Max PInv Value = 
IF(ISBLANK(SUM(InventoryPartial[PInvQuantity]))=FALSE(), 
    SUM(InventoryPartial[PInvQuantity]),
   CALCULATE(SUM(InventoryPartial[PInvQuantity]), FILTER(all(InventoryPartial), InventoryPartial[Date] = MAX(InventoryPartial[Date]))))

Here's a link to the sample file. Please use the InventoryPartial table. 

Thank you!

[v-piga-msft · Accepted solution]

Hi @Anonymous,

You could try this measure below.

Max PInv Value =
VAR vdate =
    CALCULATE (
        LASTNONBLANK ( 'InventoryPartial'[Date], MAX ( 'InventoryPartial'[Date] ) ),
        ALL ( DateTable )
    )
RETURN
    IF (
        ISBLANK ( SUM ( InventoryPartial[PInvQuantity] ) ) = FALSE (),
        SUM ( InventoryPartial[PInvQuantity] ),
        CALCULATE (
            SUM ( InventoryPartial[PInvQuantity] ),
            FILTER (
                ALL ( InventoryPartial ),
                MONTH ( InventoryPartial[Date] ) = MONTH ( vdate )
            )
        )
    )

And the output is below.

Hope it can help you!

Best Regards,

Cherry

[Anonymous]

Hi Cherry @v-piga-msft, do you mind explaining the formula? The LASTNONBLANK variable really tripped me off, I wouldn't have thought of it. (I'm still relatively new to DAX).

[Anonymous]

Thank you @v-piga-msft Cherry!