Forum Discussion

crispy_sam's avatar
crispy_sam
Frequent Visitor
2 years ago

Combining two measures into one. Inaccurate results.

I am trying to combine 2 calcluated measures into one calculated measure, but the resulting answer is incorrect. Please help me understand what is wrong or how do I go about creating a measure that would give the the correct result.
Sample .pbix file 

 

Date Table:

Date

WeekNum

Week

7/8/24

2

28

7/9/24

3

28

7/10/24

4

28

.

.

.

.

.

.

.

.

.

7/10/23

2

28

7/11/23

3

28

7/12/23

4

28

.

.

.


Measure 1: 

_SDLY Net Sales = 
VAR CurrentDayOfWeek = MAX('Date'[WeekNum])
VAR CurrentWeek = MAX('Date'[Week])
VAR LastYear = MAX('Date'[Year]) - 1
RETURN
CALCULATE( 
    [Net Sales]
    , ALL('Date')
    , 'Date'[Week] = CurrentWeek
    , 'Date'[Day of Week Number] = CurrentDayOfWeek
    , 'Date'[Year] = LastYear 
)

Measure 2: 

SDLY Net Sales = SUMX ( 'Date', [_SDLY Net Sales] )
 
Combined Measure:
Test SDLY = 
VAR CurrentDayOfWeek = MAX('Date'[Day of Week Number])
VAR CurrentWeek =  MAX('Date'[Week]) 
VAR LastYear = MAX('Date'[Year]) - 1
VAR Result = 
    CALCULATE( 
            [Net Sales],
            ALL('Date'), 
            'Date'[Week] = CurrentWeek, 
            'Date'[Day of Week Number] = CurrentDayOfWeek, 
            'Date'[Year] = LastYear
        )
RETURN
SUMX('Date', Result)

 

Resulting Table:

 

  • My goal is find the total of a metric (Net Sales) for last year during the same period.
  • The way "same period" is calcluated is different, based on the date filter, for a particular day it selects a date from last year that has the same week number for the year and day number for that week. So, same day last year for 7/8/24 is 7/10/23 as the two dates fall on week-28 and is the 2nd day of the week during their respectful year.
  • Test SDLY is a combined measure, although the individual values match the values of the two measures, the totals do not match.
  • I intented to incorporate this meaure into a calculation group in the future.
Need Help