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
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?
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
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))
Power BI — Year on Year with or Without Time Intelligence
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
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
Vote for your favorite t-shirt design now through March 28.
Find out more about the March 2023 update.
Find out more about the online and in person events happening in March!