Forum Discussion
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=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?
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 - preRollRegards,
Xiaoxin Sheng
7 Replies
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.
HI amitchandak , I have first of month dates and not end of month dates. How do I change your measure to reflect that?
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
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 - preRollRegards,
Xiaoxin Sheng
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.
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
