cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Sales by day to selected date in filter

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!

1 ACCEPTED SOLUTION
Helper I

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))
4 REPLIES 4
Helper I

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))
Super User

@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

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Helper I

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?

Helper I

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors