Filter Data Between daterange

[Anonymous]

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.

[camargos88 · Accepted solution]

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!
Ricardo

[camargos88 · Accepted solution]

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!
Ricardo

[Anonymous]

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 

[camargos88]

Hi @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

[Anonymous]

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 🙂

[Anonymous]

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 · Accepted solution]

Hi @Anonymous ,

This is just an example, there are many ways to create the date table.

Ricardo