Calculate value between two dates

[rolf1994]

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?

[acorcos]

ok, i found my way.

Its not elegant, but works.

First I created two measures:

TODAYLESS365B = (today()-364)-(day(today()))

- This returns like 01/09/2020

TODAYLESS365E = today()-365

- This returns like 07/09/2020

Then a new final measure

TTMMLASTSIS = CALCULATE(SUM(AC_TKTBYMM[COUNT]),DATESBETWEEN(AC_TKTBYMM[DATE],[TODAYLESS365B],[TODAYLESS365E]),AC_TKTBYMM[TKT_TYPE] IN {"System"})

[Greg_Deckler]

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

[rolf1994]

@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

[Greg_Deckler]

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

[rolf1994]

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

[v-ljerr-msft · Accepted solution]

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

[Anonymous]

This worked well for me thank you 🙏 @v-ljerr-msft just have one additional question. How would you write this to show the output as 0 for counts where there is no date values just yet? 

[Anonymous]

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)