Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi all,
I need to create a measure that will return my sales by date from the beginning of the year to the selected date in the filter. So what I have in mind excatly is below:
1) I have calendar table.
2) Below sample of data
Date | Sales |
11/1/2021 | 110$ |
1/1/2022 | 100$ |
1/2/2022 | 100$ |
1/3/2022 | 100$ |
3/1/2022 | 100$ |
3/2/2022 | 100$ |
4/1/2022 | 100$ |
3) When I select in the date filter, for example 12/1/2021, I need to see on the chart all sales by date since the beginning of the year for 2021.
4) When I select in the date filter, for example 4/1/2022, I need to see on the chart all sales by date since the beginning of the year 2022.
I was looking for it but I can't find anything. The result should be like on the TimlineVisual but in the filter.
Can you give me an advice how to solve that case?
THANKS!
Solved! Go to Solution.
Solved by my own 😄
Solved by my own 😄
@Misq , you want to display total then you can use a connected date table and datesytd
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
But if want to disply trend,
You need to use an independent date table for slicer
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max,-1* month(_max) )+1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
Hi amitchandak,
I have a another calendar table (CalendarAuto) with no relationship to any of tables. This measure that you provided dont work excatly as I wanted, due to the fact it only shows values for last year (img).
and if I add the filter with the dates, it not showing me values for 2013. Any suggestions?
Besides of that this measure, works for the months not for the days on X-axis
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
88 | |
86 | |
82 | |
65 | |
49 |
User | Count |
---|---|
138 | |
110 | |
104 | |
66 | |
64 |