show sales by date for month-to-date dates

[jj_0511]

Hello:

I have a Date table and Sales table linked via 'Date'[Date] --> 'Sales'[Order date].

There's a slicer on 'Date'[Date]. When a date is selected in slicer, I want to show sales by date for Month-to_date for selected date.

For example, if May-3-2023 is selected, the following result is shown:

Date                 Sales

May-1-2023      100

May-2-2023      200

May-3-2023      300

if May-10-2023 is selected, the following result is shown:

Date                 Sales

May-1-2023      100

May-2-2023      200

May-3-2023      300

...     ...

May-9-2023    200

May-10-2023  100

How to achieve this in Power BI?

Thanks.

[Ahmedx]

you need to create two date tables
one for the slicer and one for filtering sales tables

=
VAR _selectDate = MAX('Date1'[date])
VAR _Startmonth = EOMONTH(_selectDate,-1)+1
RETURN
CALCULATE (SUM('Table'[Sales]), KEEPFILTERS(Filter(ALL('Date2'), 'Date2'[date] >= _Startmonth && 'Date2'[date]<=_selectDate)))

[jj_0511]

@Ahmedx thanks, but I still have trouble understanding your solution, Would you mind creating a pbix? 

[Ahmedx]

pls try this

Sample PBIX file attached

show sales by date for month-to-date dates.pbix

[jj_0511 · Accepted solution]

Great, @Ahmedx , that helped me to get I solution I need. Thanks!

[Ahmedx]

First you need to remove the relationship between the date table and the sales table
then write like this

=
VAR _selectDate = MAX('Date'[date])
VAR _Startmonth = EOMONTH(_selectDate,-1)+1
RETURN
CALCULATE (SUM('Table'[Sales]), Filter(ALL('table'), 'table'[date] >= _Startmonth && 'table'[date]<=_selectDate))

[jj_0511]

Your calculation only gives a scalar value of Month-to-selected date sales

but I need a table showing sales by date from start of month up to selected date:

Date                 Sales

May-1-2023      100

May-2-2023      200

May-3-2023      300

...     ...

May-9-2023    200

May-10-2023  100