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

 

 

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

3 Replies

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