Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
You need to create a separate time tables to do this.
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) )
@amitchandak
I still new to powerbi, so would you be able to explain a little more on it.
thanks.
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
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.
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.
Thank you so much!
@Anonymous
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |