Forum Discussion

rolf1994's avatar
rolf1994
Helper II
9 years ago
Solved

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 ...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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. :smileyhappy:

     

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

     

    Regards

rolf1994's avatar
rolf1994
Helper II
9 years ago

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's avatar
Greg_Deckler
Community Champion
9 years ago

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

  • rolf1994's avatar
    rolf1994
    Helper II
    9 years ago
    I cannot use the calculate function when creating a new column in directquery mode. Is there any other way?
    • v-ljerr-msft's avatar
      v-ljerr-msft
      Microsoft Employee
      9 years ago

      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. :smileyhappy:

       

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

       

      Regards

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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)