DateRange between two fields

[cristiannt]

Hi all, happy new year!

I'm starting to work with this tool and I don't have a lot of knowledge about this.

My inquiry is pretty simple, I have 2 fields in a database, let's call them as START_DATE and END_DATE.

What I'd like to do is to filter the data between these two dates. For example, how many calls we received between START_DATE and END_DATE.

I tried the timeline filter, but I have the restriction to add only 1 field. So I can use the timeline with START_DATE OR END_DATE.

Is there a way to filter a daterange between 2 different field dates?

Thank you so much!

[Anonymous]

Hey @cristiannt have you gotten a solution to this problem? I am struggling too.

[v-sihou-msft]

@cristiannt

I assume you already have a calendar table with fact data on day level. Now you need to filter that calendar table context on each "StartDate+EndDate" row level. Right?

In your scenario, you just need to use StartDate and EndDate as condition in filters within calculated column formula. You can use FILTER() or DATESBETWEEN(). Please refer to my sample below:

Column 1 = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Date]>=DateRange[StartDate] && 'Table'[Date]<=DateRange[EndDate]))

Column 2 = CALCULATE(SUM('Table'[Amount]),DATESBETWEEN('Table'[Date],DateRange[StartDate],DateRange[EndDate]))

Regards,

[cristiannt]

Thank yo so much for your replies guys! @v-sihou-msft @powerbirino3 @BetterCallFrank

I think I was not enough clear with my example. The table structure I have is the following:

Order ID: int PK

Open Date: date

Closure Date: date

Amount: int

What I'd like to filter is with these 2 date fields. F.e. I'd like to check how many amounts did we get between open date 1/1 and closure date 1/3.

Thank you again!

[tonysellars]

Maybe some clarification as to what it is you are asking - when you say you would like to check how many amounts between 1/1 and 1/3, does that mean you would like the rum of amounts:

   * for all rows where the open date falls in that range?

   * for all rows where the closure date falls in that range?

   * for all rows where both the open date and the closure date fall in that range?

   * for all rows where either the open date or the closure date fall in that range?

Since you are picking a range, it is necessary to know how to compare that range to the rows you are intending to filter.

[cristiannt]

Thank you for your response @tonysellars

The third option is correct. For all rows where both the open date and the closure date fall in that range

Thanks

[powerbirino3]

Yes, first create a custom table (create it with CALENDER function) and then relate it with your table.

Then add a slicer to the dashboard and add as value the column from the Date table (the one created with CALENDER function), then in the slicer choose Range mode and you will be able to range between two dates

[BetterCallFrank]

Hi @cristiannt

can you pls post screenshots of your data and your relationships?

Thanks!

Frank