Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Show Last Available Value in Timeline Report

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 conti...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    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

v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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