Sales by day to selected date in filter

[Misq]

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!

[Misq · Accepted solution]

Solved by my own 😄

Sales to date =

var _max = MAXX(ALLSELECTED(Date1), Date1[Date])

var _min = STARTOFYEAR(Date1[Date])

return

calculate( sum(data[amount]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

[amitchandak]

@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

[Misq]

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?

[Misq]

Besides of that this measure, works for the months not for the days on X-axis