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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

Hi,

Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by Month number column.  To your table visual, drag Year and Month name from the Calendar Table.  There should be a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.  Write these measures:

Total sales = sum(Data[Sales])

Current period = calculate([Total sales],datesbetween(calendar[Date],edate(min(calendar[date]),-11),max(calendar[date])))

Previous period = calculate([Total sales],datesbetween(calendar[date],edate(min(calendar[date]),-12),min(calendar[date])-1)))

Net value = [currnet period]-[previous period]

Hope this helps.