cancel
Showing results for
Did you mean:
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
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
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!

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

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!

Helper II
I cannot use the calculate function when creating a new column in directquery mode. Is there any other way?
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

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!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors