topic Re: Filter Data Between daterange in DAX Commands and Tips

Filter Data Between daterange

Anonymous — Wed, 06 May 2020 08:26:39 GMT

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.

Re: Filter Data Between daterange

camargos88 — Wed, 06 May 2020 17:37:10 GMT

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

Re: Filter Data Between daterange

Anonymous — Thu, 07 May 2020 00:28:23 GMT

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

Re: Filter Data Between daterange

camargos88 — Thu, 07 May 2020 02:04:20 GMT

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