cancel
Showing results for
Did you mean:
Highlighted
Helper II

## Calculate value between two dates

Hi, i have the following tables in my data model (using directquery). I want to calculate the value in the ValueTable between the StartDate and EndDate.

`ValueTable:Date       Value25-2-2017  126-2-2017  127-2-2017  128-2-2017  11-3-2017   12-3-2017   13-3-2017   14-3-2017   15-3-2017   1 `

`PeriodTable:StartDate   EndDate  1-2-2017    28-2-20171-3-2017    31-3-2017`

The outcome of the formule should look like this:

1-2-2017 - 28-2-2017 = 4

1-3-2017 - 31-3-2017 = 5

Could someone help me with this?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

Hi @rolf1994,

You should be able to use the formula below to create a measure in this scenario, then show the measure on the Table/Matrix visual with PeriodTable[StartDate] and PeriodTable[EndDate] column.

```Measure =
CALCULATE (
SUM ( ValueTable[Value] ),
FILTER (
ValueTable,
ValueTable[Date] >= MIN( PeriodTable[StartDate] )
&& ValueTable[Date] <= MAX ( PeriodTable[EndDate] )
)
)
```

Regards

6 REPLIES 6
Highlighted
Super User IV
`Column = CALCULATE(SUM(ValueTable[Value]),FILTER(ValueTable,ValueTable[Date]>PeriodTable[StartDate] && ValueTable[Date]<PeriodTable[EndDate]))`

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Helper II

@Greg_Deckler

Thanks for your answer. i get the following error when i try to create the measure:

A single value for column 'StartDate' in table 'PeriodTable' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result

Highlighted
Super User IV

I created a column and it was in the PeriodTable table.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Helper II
I cannot use the calculate function when creating a new column in directquery mode. Is there any other way?
Highlighted
Microsoft

Hi @rolf1994,

You should be able to use the formula below to create a measure in this scenario, then show the measure on the Table/Matrix visual with PeriodTable[StartDate] and PeriodTable[EndDate] column.

```Measure =
CALCULATE (
SUM ( ValueTable[Value] ),
FILTER (
ValueTable,
ValueTable[Date] >= MIN( PeriodTable[StartDate] )
&& ValueTable[Date] <= MAX ( PeriodTable[EndDate] )
)
)
```

Regards

Highlighted
Helper I

I have the same but opposite setup on my tables, how would you write the measure if the periods are on the values table and the and the second table is a single date point, where you wanted to get the sum of all values where the single date falls in-between the start and end dates?

ie.

Date Table:

Date

1/15/2018

2/15/2018

3/15/2018

Values Table

Start      | End        | Value

1/01/18 | 1/31/18 | 1

1/13/18 | 3/12/18 | 1

2/14/18 | 3/13/18 | 1

so i would be looking for a result like this:

Date       | Sum

1/15/18  | 2

2/15/18  | 2

Thanks

(let me know if this is too offtopic and I will start a new thread)

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors