Previous Value By Region etc

Question

Hi&nbsp;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.&nbsp;The previous week should Match the Region, Area &amp; Date. The DeltaDate is the snapshot date as I need to track the Amount in $ and % over the snapshot dates.&nbsp;Thanks In advanced.&nbsp;RegionAreaDateDeltaDateAmountPrevious WeekNorthA1Jan-185/10/2018200&nbsp;NorthA2Jan-185/10/2018300&nbsp;NorthA2Feb-185/10/2018200&nbsp;WestA1Feb-185/10/2018100&nbsp;WestA2Mar-185/10/2018400&nbsp;WestA2Apr-185/10/2018900&nbsp;southA1Jan-185/10/201810&nbsp;southA2Jan-185/10/20182&nbsp;southA2Feb-185/10/201843&nbsp;NorthA1Jan-1828/09/201856200NorthA2Jan-1828/09/2018123300NorthA2Feb-1828/09/2018452200WestA1Feb-1828/09/2018400100WestA2Mar-1828/09/201820400WestA2Apr-1828/09/201819900southA1Jan-1828/09/20181510southA2Jan-1828/09/2018762southA2Feb-1828/09/20188943NorthA1Jan-1821/09/201810056NorthA2Jan-1821/09/2018400123NorthA2Feb-1821/09/2018900452WestA1Feb-1821/09/201810400WestA2Mar-1821/09/2018220WestA2Apr-1821/09/201840019southA1Jan-1821/09/20185615southA2Jan-1821/09/201812376southA2Feb-1821/09/201840089

walnutmercury · Answer

Any help on this would be appreciated.

anonymous · Answer

Hi&nbsp;walnutmercury,
&nbsp;
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] )
                &amp;&amp; [Area] = EARLIER ( [Area] )
                &amp;&amp; [DeltaDate] &lt; EARLIER ( [DeltaDate] )
        )
    )
RETURN
    MAXX (
        FILTER (
            ALL ( 'Sample' ),
            [Region] = EARLIER ( [Region] )
                &amp;&amp; [Area] = EARLIER ( [Area] )
                &amp;&amp; [DeltaDate] = previous
        ),
        [Amount]
    )

&nbsp;
Regards,
Xiaoxin Sheng

Forum Discussion

Previous Value By Region etc

2 Replies

Related content

Getting value for previous year based on value name and year slicer

Power BI Service Region Move

Fabric region : datacenter region or geographic region

Previous Day Value

Previous Row Value Dax

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