Forum Discussion

walnutmercury's avatar
walnutmercury
Frequent Visitor
7 years ago

Previous Value By Region etc

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...
Need Help
Tutorial Requests
walnutmercury's avatar
walnutmercury
Frequent Visitor
7 years ago
Any help on this would be appreciated.
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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