Previous Value By Region etc

[walnutmercury]

Hi

Might be very simple, but I need your help. My Dataset looks like the below, and I need to create the Previous Week Value as well as Totals by Date, by Delta Date.

The previous week should Match the Region, Area & Date. The DeltaDate is the snapshot date as I need to track the Amount in $ and % over the snapshot dates.

Thanks In advanced.

Region	Area	Date	DeltaDate	Amount	Previous Week
North	A1	Jan-18	5/10/2018	200
North	A2	Jan-18	5/10/2018	300
North	A2	Feb-18	5/10/2018	200
West	A1	Feb-18	5/10/2018	100
West	A2	Mar-18	5/10/2018	400
West	A2	Apr-18	5/10/2018	900
south	A1	Jan-18	5/10/2018	10
south	A2	Jan-18	5/10/2018	2
south	A2	Feb-18	5/10/2018	43
North	A1	Jan-18	28/09/2018	56	200
North	A2	Jan-18	28/09/2018	123	300
North	A2	Feb-18	28/09/2018	452	200
West	A1	Feb-18	28/09/2018	400	100
West	A2	Mar-18	28/09/2018	20	400
West	A2	Apr-18	28/09/2018	19	900
south	A1	Jan-18	28/09/2018	15	10
south	A2	Jan-18	28/09/2018	76	2
south	A2	Feb-18	28/09/2018	89	43
North	A1	Jan-18	21/09/2018	100	56
North	A2	Jan-18	21/09/2018	400	123
North	A2	Feb-18	21/09/2018	900	452
West	A1	Feb-18	21/09/2018	10	400
West	A2	Mar-18	21/09/2018	2	20
West	A2	Apr-18	21/09/2018	400	19
south	A1	Jan-18	21/09/2018	56	15
south	A2	Jan-18	21/09/2018	123	76
south	A2	Feb-18	21/09/2018	400	89

[walnutmercury]

Any help on this would be appreciated.

[v-shex-msft]

Hi @walnutmercury,

You can refer to following calculated column formula to get previous amount:

previous Amount =
VAR previous =
    CALCULATE (
        MAX ( Sample[DeltaDate] ),
        FILTER (
            ALL ( 'Sample' ),
            [Region] = EARLIER ( [Region] )
                && [Area] = EARLIER ( [Area] )
                && [DeltaDate] < EARLIER ( [DeltaDate] )
        )
    )
RETURN
    MAXX (
        FILTER (
            ALL ( 'Sample' ),
            [Region] = EARLIER ( [Region] )
                && [Area] = EARLIER ( [Area] )
                && [DeltaDate] = previous
        ),
        [Amount]
    )

Regards,

Xiaoxin Sheng