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

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