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,
I am creating report, displaying sales data for each month from selected date range. I have created date table. I would like to change it a little: date slicer will have only one date to choose and report will show data from last 12 months from choosen date. Anybody can help?
Solved! Go to Solution.
@annaka05 , you need the slicer to be on an independent date table for that
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
Hello @amitchandak!
I have one more question. Your solution is almoust all what I need, but can I change one more thing. Is it possible to show previous 12 months data, but from end of month based on date selected in slicer? Now it shows previous 12 months, but last month shows only sales of selected date. So, when I have selected e.g. 2022/11/05 on slicer, last month sales shows only sales till this date. I would like to change it, that I will choose this date, I will see sales till end of November 2022.
Regards,
Anna
@annaka05 , you need the slicer to be on an independent date table for that
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
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 |
---|---|
106 | |
104 | |
78 | |
68 | |
61 |
User | Count |
---|---|
144 | |
106 | |
106 | |
82 | |
70 |