Wrong total with IF MAX measure

[joshua1990]

Hi all!

I have a datamodel with 4 tables:

• 2 dimensional tables
  • Calendar (Date, Year, Month)
  • Master Data (Article)
• 2 transactional tables
  • Past Orders (Article, Date, Value)
  • Open Orders (Article, Date, Value)

Both transactional tables are related to both dimensional tables.

 Now I have build a simple matrix and a measure with the following logic:

Orders =
VAR _Orders = [Order Value]
RETURN
IF(MAX('Open Orders'[Date]) > MAX('Past Orders'[Date]), _Orders , 0)

The matrix has Months as columns. The result of the measure is displayed correctly for each month.

But the sum / total is 0 on the right.

What is the issue here?

[tamerj1]

Hi @joshua1990 

please try

Orders =
SUMX (
VALUES ( 'Calendar'[YearMonth] ),
CALCULATE (
VAR _Orders = [Order Value]
VAR _MaxOpenDate =
MAX ( 'Open Orders'[Date] )
VAR _MaxPastDate =
MAX ( 'Past Orders'[Date] )
RETURN
IF ( _MaxOpenDate > _MaxPastDate, _Orders, 0 )
)
)

[joshua1990]

@tamerj1 : I really don't know why, but still the same issue.... 

[tamerj1]

@joshua1990 
Would you please provide a screenshot to help us visualize the problem.

[BeaBF]

@joshua1990 Hi!

try this formula:

Orders =
VAR _Orders = [Order Value]
VAR _MaxOpenDate = MAX('Open Orders'[Date])
VAR _MaxPastDate = MAX('Past Orders'[Date])
RETURN IF(_MaxOpenDate > _MaxPastDate, _Orders, IF(ISBLANK(_MaxOpenDate) || ISBLANK(_MaxPastDate), BLANK(), 0))

This measure will return the order value for all months, but only include the value in the calculation if the 'Open Orders' table has a later date than the 'Past Orders' table. It also includes a check for blank values in case there are missing dates in either of the tables. This should give you the correct sum/total for the 'Orders' measure.

BBF

[joshua1990]

@BeaBF : Thanks a lot for your support, but still the same issue. No SUM / Total on the right. Still a 0.

Bot tables are linked 1:n to the calendar table.

[BeaBF · Accepted solution]

@joshua1990 

The issue could be related to the context in which the measure is being evaluated. When you add up the values of the measure, the total might be showing as zero due to the filter context applied on the table.

To fix this issue, you can try using the "ALL" function to remove the filter context from the table and evaluate the measure across all rows.

Here's an updated measure that should work:

Orders =
VAR _Orders = [Order Value]
RETURN
IF(
MAX('Open Orders'[Date]) > MAX('Past Orders'[Date]),
_Orders,
0
)

Total Orders =
CALCULATE(
[Orders],
ALL('Calendar'[Month])
)

BBF