cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
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 Value
25-2-2017 1
26-2-2017 1
27-2-2017 1
28-2-2017 1
1-3-2017 1
2-3-2017 1
3-3-2017 1
4-3-2017 1
5-3-2017 1 

 

PeriodTable:
StartDate EndDate
1-2-2017  28-2-2017
1-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

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. Smiley Happy

 

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

r1.PNG

 

Regards

View solution in original post

6 REPLIES 6
Super User IV
Super User IV

Column = CALCULATE(SUM(ValueTable[Value]),FILTER(ValueTable,ValueTable[Date]>PeriodTable[StartDate] && ValueTable[Date]<PeriodTable[EndDate]))

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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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

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


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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. Smiley Happy

 

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

r1.PNG

 

Regards

View solution in original post

Hi @v-ljerr-msft

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)

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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