topic Re: Calculate amount of orders without blanks and gets previous value in DAX Commands and Tips

Calculate amount of orders without blanks and gets previous value

bburcu — Mon, 26 Dec 2022 08:03:59 GMT

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ı]))

)

Re: Calculate amount of orders without blanks and gets previous value

Mahesh0016 — Mon, 26 Dec 2022 08:12:03 GMT

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.

Re: Calculate amount of orders without blanks and gets previous value

bburcu — Mon, 26 Dec 2022 11:39:35 GMT

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

Re: Calculate amount of orders without blanks and gets previous value

tamerj1 — Mon, 26 Dec 2022 13:55:05 GMT

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

Re: Calculate amount of orders without blanks and gets previous value

bburcu — Mon, 26 Dec 2022 14:12:38 GMT

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.

Re: Calculate amount of orders without blanks and gets previous value

tamerj1 — Mon, 26 Dec 2022 16:15:24 GMT

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 )

Re: Calculate amount of orders without blanks and gets previous value

bburcu — Mon, 26 Dec 2022 18:59:00 GMT

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

Re: Calculate amount of orders without blanks and gets previous value

tamerj1 — Mon, 26 Dec 2022 19:41:25 GMT

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.

Re: Calculate amount of orders without blanks and gets previous value

bburcu — Tue, 27 Dec 2022 05:58:01 GMT

It's work but there is an another problem 🙂

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

Re: Calculate amount of orders without blanks and gets previous value

tamerj1 — Tue, 27 Dec 2022 07:13:56 GMT

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 )