How to get the last non blank value based on the date slicer

[latheesh2305]

Dear Techies,

Let's assume I have a table as shown below. I wanted to get the last valid sales (without blank) for each product and calculate variance for each row.

Product	Date	Sales
A	6/1/2022	100
A	6/15/2022	300
A	6/25/2022	200
A	6/28/2022
A	9/13/2022	300
A	9/28/2022	500
A	9/30/2022
A	10/5/2022	200
A	10/12/2022	300
A	10/17/2022
B	6/1/2022	250
B	6/15/2022
B	6/25/2022	350
B	6/28/2022	500

Let's say I have a Month-Year slicer and I chose "Jun 2022" then the expected output should be,

Product	Date	Sales	Last Sale	Variance
A	6/1/2022	100	200	-100
A	6/15/2022	300	200	100
A	6/25/2022	200	200	0
A	6/28/2022		200
B	6/1/2022	250	500	-250
B	6/15/2022		500
B	6/25/2022	350	500	-150
B	6/28/2022	500	500	0

If you notice the above output, for the product A, Last sales was taken from 6/25/2022 although the max date is 6/28/2022 because there was no sales on 6/28/2022. But that is not the case for the product B, where we do have a valid sales for the max date 6/28/2022. 

Let's say I don't select anything on my Month-Year slicer then the expected output should be,

Product	Date	Sales	Last Sale	Variance
A	6/1/2022	100	300	-200
A	6/15/2022	300	300	0
A	6/25/2022	200	300	-100
A	6/28/2022		300
A	9/13/2022	300	300	0
A	9/28/2022	500	300	200
A	9/30/2022		300
A	10/5/2022	200	300	-100
A	10/12/2022	300	300	0
A	10/17/2022		300
B	6/1/2022	250	500	-250
B	6/15/2022		500
B	6/25/2022	350	500	-150
B	6/28/2022	500	500	0

Here the last sales for the product is based on the date 10/12/2022 and for the product B it is 6/28/2022.

I'm using Analysis services live connection and I tried various DAX functions but I'm not able to calculate the "Last Sales". Any suggestions or solutions will be of great help.

Thanks,

Latheesh

[v-yueyunzh-msft · Accepted solution]

Hi, @latheesh2305 

According to your description, you want to display the date of the third week before by a certain date. Right?

Here are the steps you can follow：

(1)This is my test data:

(2) We can create two measures : “Last Sales”  and “Variance”

Last Sales =
VAR _current_product =
    SELECTEDVALUE ( 'Test'[Product] )
VAR _product_table =
    CALCULATETABLE (
        'Test',
        'Test'[Product] = _current_product,
        ALLSELECTED ( 'Test'[Date] )
    )
VAR _max_date =
    MAXX ( FILTER ( _product_table, [Sales] <> BLANK () ), [Date] )
VAR _max_sales =
    SUMX (
        FILTER (
            ALL ( 'Test' ),
            'Test'[Date] = _max_date
                && 'Test'[Product] = _current_product
        ),
        [Sales]
    )
RETURN
    _max_sales

Variance = IF(SUM([Sales])=BLANK(),BLANK(), SUM([Sales]) -[Last Sales])

(3)We can put the measures and the fields in the visual , then we can meet your need:

If this method can't meet your requirement, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@latheesh2305 , Use a date table joined with your table

this month last value= CALCULATE(lastnonblankvalue(Table[Date], SUM(Table[Sales]) ) ,DATESMTD(ENDOFMONTH('Date'[Date])))