Cumulative sales last week vs this week

[Anonymous]

Hi all,

I am calculating Cumulative sales for last week to compare with cumulative this week sales but I am not getting right output.

Cumulative Sales last week =
CALCULATE (
[Total Sales Last Week],
FILTER (
ALL( PBIDateTable[Week number] ),
PBIDateTable[Week number] < MAX ( PBIDateTable[Week number] )
)
)

[v-piga-msft · Accepted solution]

Hi @Anonymous，

 By my test, you could create the two measure to calculate Cumulative sales for last week and cumulative this week sales by slicer.

Cumulative Sales of this week =
CALCULATE (
    SUM ( PBIDatetable[Sales] ),
    DATESBETWEEN ( 'Table'[Date], MAX ( 'Table'[Date] ) - 7, MAX ( 'Table'[Date] ) ),
    FILTER (
        ALL ( PBIDatetable ),
        'PBIDatetable'[Accountlevel] = MAX ( 'PBIDatetable'[Accountlevel] )
            && WEEKNUM ( 'PBIDatetable'[date] ) = WEEKNUM ( MIN ( 'PBIDatetable'[date] ) )
    )
)

Cumulative Sales last_week =
VAR startoflastweek =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[WeekNum]
                = WEEKNUM ( MIN ( 'Table'[Date] ) ) - 1
        )
    )
RETURN
    CALCULATE (
        SUM ( PBIDatetable[Sales] ),
        DATESBETWEEN ( 'Table'[Date], startoflastweek, MIN ( 'Table'[Date] ) - 7 ),
        FILTER (
            ALL ( PBIDatetable ),
            'PBIDatetable'[Accountlevel] = MAX ( 'PBIDatetable'[Accountlevel] )
                && WEEKNUM ( 'PBIDatetable'[date] )
                    = WEEKNUM ( MIN ( 'PBIDatetable'[date] ) ) - 1
        )
    )

The picture of result is below.

You also could refer to my pbix file here.

If you need additional help please share some data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

Best Regards,

Cherry

[Anonymous]

@v-piga-msft      Hello  -  This formula looks like something I could use but it seems to me it is not working properly.  

The cumulative result for the prior week is showing a "2"  in the table below, rather than the cumulative sum (14).  But I'm not exactly sure how to fix the formula to get the correct result.    Anyone know how to fix this?  

Thanks