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.
Dear Community,
I have sales data dating back from 2010. I used line and stacked column chart with total sales(as a bar) by year and Growth % (as a line). Everything is working out great until we are here today at 9/06/2021.
The total of sales from 1/1/2021 to 9/6/2021 is $ 200,000, which is great, Power BI is showing that correctly.
But the total of sales from 1/1/2020 to 9/6/2020 is $ 145,000, but Power BI is taking $ 160,000 (1/1/2020 to 30/6/2020). It is taking full month for June 2020 which doesn't make sense with the Dax I am using. This is understating the growth % that I am showing.
Following are the Dax I am using
Solved! Go to Solution.
@shekhar_shres , if date range is not selected on page, try a measure like
Sales Last Year =
var _max1 = maxx(allselected(sales), sales[date])
var _max = date(year(_max1)-1, month(_max), day(_max))
return
CALCULATE('Total Measures'[TotalSales], DATEADD('Date Table'[Date], -1, YEAR),filter('Date Table','Date Table'[Date]<=_max ))
Have you solved your problem? If yes, kindly accept it as the solution. Otherwise, please provide more details about your problem so that we can work on it further. Thanks😃
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.
"It is taking full month for June 2020" —— because function DATEADD() will return the last whole month, FYI:
https://docs.microsoft.com/en-us/dax/dateadd-function-dax
One quick example,
if you want period (1/1/2020 to 30/6/2020), use function filter() mentioned by amit.
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.
@shekhar_shres , if date range is not selected on page, try a measure like
Sales Last Year =
var _max1 = maxx(allselected(sales), sales[date])
var _max = date(year(_max1)-1, month(_max), day(_max))
return
CALCULATE('Total Measures'[TotalSales], DATEADD('Date Table'[Date], -1, YEAR),filter('Date Table','Date Table'[Date]<=_max ))
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |