Getting Last 6 Month Sales

[Anonymous]

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
    )
)

[amitchandak · Accepted solution]

@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))

[Ashish_Mathur · Accepted solution]

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.

[amitchandak · Accepted solution]

@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))