Forum Discussion
Filter Data Between daterange
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
Thanks for the help on this one. It was driving me a bit mad.
Can I check the process you did to create the Date Table? I want to recycle your code e.g.
Date = CALENDAR(EOMONTH(MIN(Sales[Date]). -1) +1, EOMONTH(MAX(Sales[Date]), 0))Thank you
Community ChampionHi 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
Thanks Ricardo.
What is the best way to create the data table? Do I just press the entre data option in the Home>Data icon?
Can you share the steps you did to create this data table. This was what I was trying to ask. Sorry I was not clear.
Thanks for those articles I will ingest them 🙂
Oh I think you shared that article https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Can you share that code snippet?
- camargos88
Hi Anonymous ,
This is just an example, there are many ways to create the date table.
Ricardo