Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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)))
@Ahmedx thanks, but I still have trouble understanding your solution, Would you mind creating a pbix?
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))
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |