Forum Discussion

cristiannt's avatar
cristiannt
Frequent Visitor
9 years ago

DateRange between two fields

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_D...
v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

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,