Forum Discussion

bml123's avatar
bml123
Post Patron
4 years ago
Solved

Net Value measure

Hi,   I have sales data for each month and I need to calculate the net growth. for example, for Dec-21, NetValue=sumofsales(Jan-21 to Dec-21) - sumofsales(Dec-20 to Nov-21) for Jan-22 NetValue=s...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    HI bml123,

    Please take a look at the following measure formula if it is suitable for your requirement:

    NetValue =
VAR currDate =
    MAX ( date[Date] )
VAR Roll =
    CALCULATE (
        SUM ( Sales[Sales Amount] ),
        FILTER (
            ALLSELECTED ( Sales ),
            Sales[Date]
                >= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 11, DAY ( currDate ) )
                && Sales[Date] <= currDate
        )
    )
VAR preRoll =
    CALCULATE (
        SUM ( Sales[Sales Amount] ),
        FILTER (
            ALLSELECTED ( Sales ),
            Sales[Date]
                >= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 12, DAY ( currDate ) )
                && Sales[Date]
                    <= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) )
        )
    )
RETURN
    Roll - preRoll

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
4 years ago

HI bml123,

Please take a look at the following measure formula if it is suitable for your requirement:

NetValue =
VAR currDate =
    MAX ( date[Date] )
VAR Roll =
    CALCULATE (
        SUM ( Sales[Sales Amount] ),
        FILTER (
            ALLSELECTED ( Sales ),
            Sales[Date]
                >= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 11, DAY ( currDate ) )
                && Sales[Date] <= currDate
        )
    )
VAR preRoll =
    CALCULATE (
        SUM ( Sales[Sales Amount] ),
        FILTER (
            ALLSELECTED ( Sales ),
            Sales[Date]
                >= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 12, DAY ( currDate ) )
                && Sales[Date]
                    <= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) )
        )
    )
RETURN
    Roll - preRoll

Regards,

Xiaoxin Sheng