Forum Discussion
Filter Data Between daterange
I attempted to follow this thread on this channel but I think my issue is easier.
I have the below table (dates) that shows the beginning and ending of a month
| StartDate | EndDate |
| 01/10/2020 | 31/10/2020 |
| 01/11/2020 | 30/11/2020 |
My other table (sales) has a date between these ranges
| Date | Amount | Store |
| 08/10/2020 | 88 | StoreA |
| 11/10/2020 | 92 | StoreB |
| 05/11/2020 | 110 | StoreA |
I would like a date slicer that returns the sum based on filtered data in table (Sales)
Q1. Would it best to use a SUMX aggregrator?
Q2. How could I create a relationship between these two tables based only on the daterange?
I am a little stuck and would appreciate the help.
Hi Anonymous,
You don't need a table with start and end. You can create just a date table and relate them.
Use a slicer, you are gonna have the start/end dates to filter. Also, you can create more columns on your date table like month/year and use it on slicers.
Check this file: Download PBIX
Did I answer your question? Mark my post as a solution!
RicardoHi Anonymous ,
This is just an example, there are many ways to create the date table.
Ricardo
6 Replies
Hi Anonymous,
You don't need a table with start and end. You can create just a date table and relate them.
Use a slicer, you are gonna have the start/end dates to filter. Also, you can create more columns on your date table like month/year and use it on slicers.
Check this file: Download PBIX
Did I answer your question? Mark my post as a solution!
RicardoHi Anonymous,
Sure.
The function CALENDAR receives 2 parameters (start and end dates), the code just get the min and max of your sales date and the function EOMONTH returns the end of the month with the number of months you need.
Example:
EOMONTH(MIN(Sales[Date]). -1) +1
This one goes to the end of the min month of your sales tables, but the parameter is -1, so it will go back 1 month. Also it's adding 1 day to this date.
Min Date => 2020-01-01
EOMONTH => 2020-01-31 // This is the same as EOMONTH(MIN(Sales[Date]); -0))
EOMONTH with -1 => 2019-12-31
(EOMONTH with -1) + 1 => 2019-12-31 + 1 => 2020-01-01
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://docs.microsoft.com/en-us/dax/calendar-function-dax
Also check this link for time-intel functions, you can enrich you report with them:
https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax
Ricardo