measure for total sales for 12 months

[Anonymous]

hi all
I have a file with column of sales value & dates & product name.

I am trying to use a date & product slicer to create an measure that calculate the total sales for 12 months.
So when user select product A and date:1Feb2018, it will calculate the total sale from Feb2018 to Jan2019 (12 mth period)

I tried the following measure but it is wrong.
sales12months = CALCULATE(SUM(mydata[Value],DATESINPERIOD(mydata[Date], LASTDATE('mydata'[Date]), 12, MONTH)))

Can anyone advise.

Thank you.

[amitchandak]

You need to create a separate time tables to do this.

[amitchandak]

Create a time dimension table

Join it with your table on a date field.

Use slicer on date of the time dimension.

They use a filter from time dimension and manipulate them to get data 

Example

Sales Before QTR = 
CALCULATE(SUMx(FILTER(sales,and(Sales[Order_Date]>= STARTOFQUARTER(DATEADD(STARTOFQUARTER(OrderTime[Order Date]),-1,DAY)) && Sales[Order_Date]<= DATEADD(STARTOFQUARTER(OrderTime[Order Date]),-1,DAY),
Sales[Requested_Date]>= STARTOFQUARTER(OrderTime[Order Date]) && Sales[Requested_Date]<= STARTOFQUARTER(OrderTime[Order Date])
)),Sales[Sales]),CROSSFILTER(Sales[Order_Date],OrderTime[Order Date],None))

TY Sales = ( 
VAR _Last_Year = CONCATENATE([Report Year],"")
return 
calculate(sum(Sales[Sales]),OrderTime[Order Year] =_Last_year)
)

[Anonymous]

@amitchandak 
I still new to powerbi, so would you be able to explain a little more on it.

thanks.

[Anonymous]

sales12months = 
Var Date1 = EDATE(SELECTEDVALUE(mydata[Date1]),12)
Return
CALCULATE(SUM(mydata[Value]),FILTER(ALL(mydata[Date1]),mydata[Date1]>=SELECTEDVALUE(mydata[Date1]) && mydata[Date1] <= Date1))

Here you go, amend your sales12months measure as the above, this should work with you.

I have also uploaded the pbix file here

[Anonymous]

@Anonymous 

hihi...i tried the following, but the value look wrong?
For example: product A, date 1=Jan 2018, so the 12 month total should be 824 but it showing 847.

any idea? thank so much.

[Anonymous · Accepted solution]

The formula for the measure calculate the "ToDate" by adding 12 months to the date filter value, so i presume it included January 2019 in the period.

you can amend the formula by using 11, instead of 12 for the EDATE Formula.

[Anonymous]

Thank you so much!
@Anonymous 

[Anonymous]

@amitchandak would you be able to advise on the steps?

Thank you.