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

amitchandak's avatar
amitchandak
Super User
4 years ago

bml123 , Create a date table and join with date. date table should have all dates

 


To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4

bml123's avatar
bml123
Post Patron
4 years ago

Hi amitchandak ,

 

I have used calendar table and joined the date to my fact table and used your measures, but it's not pulling any data