Displaying previous value instead of blank

[JKoivu]

I have 3 tables:

1. Products
2. Product quantity by date
3. Calendar

On my report I have a date slicer from calendar where I can select a single date. Then I have a table visual that displays ALL products, regardless if they have a quantity on the selected date. But instead of a blank quantity, I want to display what the previous quantity was. 

Here's the quantity by date:

And products:

My report looks like this:

As you can see, Product 1 has blank quantity because no data for selected date is available. Previous quantity should display 24, which is the quantity on 01/07/2021. The (useless) DAX I used for the previous quantity is:

Previous quantity = IF(
    ISBLANK('Quantity by date'[Quantity]),
    CALCULATE(
        MAX('Quantity by date'[Quantity]),
        FILTER(
            ALL('Quantity by date'),
           'Quantity by date'[Product ID] = EARLIER('Quantity by date'[Product ID])
        )
    ), 
    CALCULATE(
        MAX('Quantity by date'[Quantity]),
        FILTER(
            ALL('Quantity by date'),
            'Quantity by date'[Date] < EARLIER('Quantity by date'[Date])
            && 'Quantity by date'[Product ID] = EARLIER('Quantity by date'[Product ID])
        )
    )
)

Here's the PBIX file:

https://drive.google.com/file/d/1tMBMXGhk2jt76pakOgzfEoNf0N1leql1/view?usp=sharing

[v-jingzhang · Accepted solution]

Hi @JKoivu 

You can use a measure to get the result.

Previous quantity = 
VAR __previousDate =
    CALCULATE (
        MAX ( 'Quantity by date'[Date] ),
        FILTER (
            ALL ( 'Quantity by date' ),
            'Quantity by date'[Date] < MAX ( 'Calendar'[Date] )
                && 'Quantity by date'[Product ID] = MAX ( 'Products'[Id] )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Quantity by date'[Quantity] ),
        FILTER (
            ALL ( 'Quantity by date' ),
            'Quantity by date'[Date] = __previousDate
                && 'Quantity by date'[Product ID] = SELECTEDVALUE ( 'Products'[Id] )
        )
    )

If you calculate previous quantity in a calculated column, you should also use a measure to get the corresponding previous quantity column value when you hope to use a slicer to switch it.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[CNENFRNL]

Qty = SUM( 'Quantity by date'[Quantity] )

Prev Qty = 
CALCULATE( [Qty], PREVIOUSDAY( 'Calendar'[Date] ) )

[JKoivu]

@CNENFRNL Thanks for your reply, this works great on this example! Though I forgot to mention that there can be gaps of several days, so the previous quantity could be from many days ago, not just previous. Any tips on how to achieve that?

[JKoivu]

I still haven't figured this out, can anyone help me?