Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
https://www.youtube.com/watch?v=km41KfM_0uA

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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors