Calculate amount of orders without blanks and gets previous value

[bburcu]

Dear all,

I have a problem that I'm handling nowadays. 

As you can see in the picture below, there are some order amounts that come empty. As in the example below, in the data set of order amounts, there are no records for the dates 18.03.2022, 19.03.2022, 20.03.2022 and 21.03.2022. However, I need to print the previous value instead of the empty order amounts. I developed a formula for this as follows. However, this formula creates a performance problem. The report's performance has slowed. Do you have any other suggestions in this situation?

Order amount w/o blanks = IF(AND(MAXX(Date,Date[Date])<[Week start date by week],ISBLANK([Sipariş miktarı max per Tarih])),

CALCULATE(LASTNONBLANK(Merge1[Order amount],[Sipariş miktarı max per Tarih]),FILTER(ALL('Date'), Date[Date] <= MAXX(Date,Date[Date]))), SUMX(Merge1, 'Merge1'[Order amount] ))

Sipariş miktarı max per Tarih =

MAXX(

    KEEPFILTERS(VALUES('Tarih'[Tarih])),

    CALCULATE(SUM('Merge1'[Sipariş miktarı]))

)

[Mahesh0016]

VAR PreviousRow = CALCULATE( IF(isblank([Order Amount]), OFFSET( -1, ALLSELECTED('Table'[Order Amount]), ORDERBY('Table'[Order Amount], ASC) ) ))
@bburcu If this post helps, please consider accept as solution to help other members find it more quickly.

[bburcu]

Thank you for your answer but it doesn't work. Also I modified your measure and just one row (18.03.2022) is work. You can see in below picture.

Siparis miktarı güncel = IF(ISBLANK([Measure]) ,

    CALCULATE(

        [Measure],

        OFFSET(

        -1,

            ALL('Tarih'[Tarih]),

            ORDERBY('Tarih'[Tarih], ASC)

        ),

        ALL('Tarih')),SUMX('Table','Table'[Sipariş mikt.]))

Note: Sipariş mikt. = Order amount

Measure = CALCULATE(SUM('Table'[Sipariş mikt.]))

[tamerj1]

Hi @bburcu 
Which column are you placing in the columns of the matrix? From which table?

[bburcu]

Date info of rows are from date table which created query.

"Hafta" info of columns are from Table (main dataset). There are no some records in the main table but appear the blanks rows relational with date table.
Two date info especially weeks are different each other.

[tamerj1]

@bburcu 

Please try

Siparis miktarı güncel =
VAR LastRecordsWithData =
CALCULATETABLE (
TOPN ( 1, 'Table', 'Table'[Tarih] ),
ALL ( 'Tarih' ),
VALUES ( 'Tarih'[Hafta] )
)
VAR PreviousValue =
SUMX ( LastRecordsWithData, 'Table'[Sipariş mikt.] )
RETURN
COALESCE ( [Measure], PreviousValue )

[bburcu]

I'm trying COALESCE function return 1 instead of blank values. "PreviousValue" doesn't work obviously 😞

[tamerj1]

@bburcu 

Please look closely at my code and notice when I use 'Table' and when I use 'Tarih'

Also I sid not use [Measure] inside SUMX rather I used the column reference directly. Please try exactly as proposed and let me know if it works. 

[bburcu]

It's work but there is an another problem 🙂 

week 12 should be continue with 20, is there any suggestion for this issue? 

[tamerj1 · Accepted solution]

@bburcu 
Please try

Siparis miktarı güncel =
VAR CurrentDate =
    MAX ( 'Tarih'[Tarih] )
VAR LastRecordsWithData =
    CALCULATETABLE (
        TOPN ( 1, 'Table', 'Table'[Kayet tarihi] ),
        ALL ( 'Tarih' ),
        'Tarih'[Tarih] < CurrentDate
    )
VAR PreviousValue =
    SUMX ( LastRecordsWithData, 'Table'[Sipariş mikt.] )
RETURN
    COALESCE ( [Measure], PreviousValue )