Forum Discussion
Getting Last 6 Month Sales
I want to get the last 6 months sales ending on the last day of last month from the current date. Following code is what I use. Is there any better suggestion to improve this?
6M pri sales value =
CALCULATE (
SUM ( FactPrimarySales[NetValue] ),
DATESINPERIOD (
DimDate[Date],
CALCULATE (
LASTDATE ( DimDate[Date] ),
DimDate[MonthNo]
= MONTH ( TODAY () ) - 1,
DimDate[Year] = YEAR ( TODAY () )
),
-6,
MONTH
)
)
Anonymous , Try a measure like this with help from date table
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1),-6,MONTH))
Hi,
If today is December 25, 2020, then i assume that "last day of last month from the current date" should mean 6 months ended November 30, 2020. If my assumption is correct, then try this measure:
=calculate(sum(Sales[Sales Amount]),datesbetween(calendar[date],EDATE(EOMONTH(today(),-1)+1,-6),EOMONTH(today(),-1)))
Hope this helps.
2 Replies
Anonymous , Try a measure like this with help from date table
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1),-6,MONTH))
Hi,
If today is December 25, 2020, then i assume that "last day of last month from the current date" should mean 6 months ended November 30, 2020. If my assumption is correct, then try this measure:
=calculate(sum(Sales[Sales Amount]),datesbetween(calendar[date],EDATE(EOMONTH(today(),-1)+1,-6),EOMONTH(today(),-1)))
Hope this helps.