Measure fields displaying blank or incorrect calculations

[finzilla]

PROBLEM: Some of the measure columns that have the exact same formula as others are returning blank (#3) or incorrect (#4) values, and I cannot figure out why.

SCENARIO:   Here are the 3 tables (formatted to look like 1) that I've created along with numbered identifiers to explain them:

Since the client wants to see data in a this week over last week format (#1), I created a table of calculated values called Weekly Sales which draws on the custom query for 'Sales Transactions'[ThisWeek] and 'Sales Transactions'[LastWeek]:

Weekly Sales = SUMMARIZE(
FILTER('Sales Transactions', OR('Sales Transactions'[ThisWeek] = "1",'Sales Transactions'[LastWeek] = "1")),
'Sales Transactions'[ThisWeek],'Sales Transactions'[LastWeek],'Sales Transactions'[WeekdayNum],'Sales Transactions'[StoreId],
"Monday",IF('Sales Transactions'[WeekdayNum]=1,SUM('Sales Transactions'[Item Total Paid]),0),
"MondayTW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 1,'Sales Transactions'[ThisWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"MondayLW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 1,'Sales Transactions'[LastWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"Tuesday",IF('Sales Transactions'[WeekdayNum]=2,SUM('Sales Transactions'[Item Total Paid]),0),
"TuesdayTW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 2,'Sales Transactions'[ThisWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"TuesdayLW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 2,'Sales Transactions'[LastWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"Wednesday",IF('Sales Transactions'[WeekdayNum]=3,SUM('Sales Transactions'[Item Total Paid]),0),
"WednesdayTW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 3,'Sales Transactions'[ThisWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"WednesdayLW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 3,'Sales Transactions'[LastWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"Thursday",IF('Sales Transactions'[WeekdayNum]=4,SUM('Sales Transactions'[Item Total Paid]),0),
"ThursdayTW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 4,'Sales Transactions'[ThisWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"ThursdayLW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 4,'Sales Transactions'[LastWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"Friday",IF('Sales Transactions'[WeekdayNum]=5,SUM('Sales Transactions'[Item Total Paid]),0),
"FridayTW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 5,'Sales Transactions'[ThisWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"FridayLW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 5,'Sales Transactions'[LastWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"Saturday",IF('Sales Transactions'[WeekdayNum]=6,SUM('Sales Transactions'[Item Total Paid]),0),
"SaturdayTW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 6,'Sales Transactions'[ThisWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"SaturdayLW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 6,'Sales Transactions'[LastWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"Sunday",IF('Sales Transactions'[WeekdayNum]=7,SUM('Sales Transactions'[Item Total Paid]),0),
"SundayTW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 7,'Sales Transactions'[ThisWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"SundayLW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 7,'Sales Transactions'[LastWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"WTD",SUM('Sales Transactions'[Item Total Paid]),
"WTDLW",IF('Sales Transactions'[LastWeek] = "1",SUM('Sales Transactions'[Item Total Paid]),0),
"WTDTW",IF('Sales Transactions'[ThisWeek] = "1",SUM('Sales Transactions'[Item Total Paid]),0),
"Description",IF('Sales Transactions'[ThisWeek],"Sales This Week","Sales Last Week")
)

And within that table, there are a number of measures for each day of the week plus WTD (#2) similar to:

SaturdayDiff = CALCULATE(SUMX('Weekly Sales','Weekly Sales'[SaturdayTW]-'Weekly Sales'[SaturdayLW]))

And more measures to calculate the diff in a % format similar to:

SatDiffPct = CALCULATE(DIVIDE(SUMX('Weekly Sales','Weekly Sales'[SaturdayTW] - 'Weekly Sales'[SaturdayLW]),SUMX('Weekly Sales','Weekly Sales'[Saturday])))

[v-yulgu-msft]

Hi @finzilla,

Does problem persist if testing with different data still using above measures?

Regards,

Yuliana Gu