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
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?

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

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago
    Column = CALCULATE(SUM(ValueTable[Value]),FILTER(ValueTable,ValueTable[Date]>PeriodTable[StartDate] && ValueTable[Date]<PeriodTable[EndDate]))
    • 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.

  • acorcos's avatar
    acorcos
    New Member
    4 years ago

    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"})