SUM of MAX date from last week

[1b0r90]

I have a dataset which is upated 3 times a week, by appending the new data at the end of the dataset. I need to display the movement of the values in a table comparing the SUM of values from latest day this week and last week.

The data looks like this:

Customer	Report Date	Value
A	13/05/2020	5
B	13/05/2020	10
A	11/05/2020	2
B	11/05/2020	12
A	08/05/2020	3
B	08/05/2020	15
A	06/05/2020	4
B	06/05/2020	14

I need to create a measure that will give me the SUM of MAX(Report Date) for most recent week and for most recent week -1.

This is what I come up with so far, but it sums up the whole last week of data. I created a calculated column (WeekNum), which I would also like to avoid if possible.

Last Week =

VAR CurrentWeek = MAX('Main table'[WeekNum])

RETURN

CALCULATE(

SUM('Main table'[Value]),

FILTER('Main table', 'Main table'[WeekNum]=CurrentWeek-1))

[1b0r90]

@amitchandak Your solution gave me an error, but it helped me to end up something very similar to what @v-gizhi-msft suggested, so I will mark that one as the solution.

Thank you both for your inputs!

[v-gizhi-msft · Accepted solution]

Hi,

Please take following steps:

1)Create this column:

WeekNo = WEEKNUM('Table'[Report Date])

2)Try these two measures:

Current_Week = 
VAR a =
    CALCULATE (
        MAX ( 'Table'[Report Date] ),
        FILTER ( 'Table', 'Table'[WeekNo] = WEEKNUM ( TODAY () ) )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[WeekNo] = WEEKNUM ( TODAY () )
                && 'Table'[Report Date] = a
        )
    )

Last_Week = 
VAR a =
    CALCULATE (
        MAX ( 'Table'[Report Date] ),
        FILTER ( 'Table', 'Table'[WeekNo] = WEEKNUM ( TODAY () ) - 1 )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[WeekNo]
                = WEEKNUM ( TODAY () ) - 1
                && 'Table'[Report Date] = a
        )
    )

3)The result shows:

See my attached pbix file.

Best Regards,

Giotto

[amitchandak]

Try like

This Week Measure =
VAR __Week = MAX('Table'[WeekNum])
VAR __id = MAX ( 'Table'[Customer] )
VAR __date = CALCULATE ( MAX( 'Table'[date] ), ALLSELECTED ( 'Table' ), 'Table'[Customer] = __id )
RETURN CALCULATE ( Sum ( 'Table'[Value] ), VALUES ( 'Table'[Customer ), 'Table'[Customer] = __id, 'Table'[date] = __date,'Table'[WeekNum] =__Week )

Last week Measure =
VAR __Week = MAX('Table'[WeekNum])-1
VAR __id = MAX ( 'Table'[Customer] )
VAR __date = CALCULATE ( MAX( 'Table'[date] ), ALLSELECTED ( 'Table' ), 'Table'[Customer] = __id )
RETURN CALCULATE ( Sum ( 'Table'[Value] ), VALUES ( 'Table'[Customer ), 'Table'[Customer] = __id, 'Table'[date] = __date,'Table'[WeekNum] =__Week )