Net Value measure

[bml123]

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=sumofsales(Feb-21 to Jan-22) - sumofsales(Jan-21 to Dec-21)

The first sum is basically sum of sales of current month until 12 months back from current month 

the second sum is sum of sales of previous month until 12 months back from previous month

How do I achieve that?

[v-shex-msft]

Hi @bml123,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Ashish_Mathur]

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.

[v-shex-msft · Accepted solution]

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]

@bml123 , Try measure like these with date table

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

Rolling 12 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1) ,-12,MONTH))

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.

[bml123]

HI @amitchandak , I have first of month dates and not end of month dates. How do I change your measure to reflect that?

[amitchandak]

@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-bd52912a5b...

[bml123]

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