Forum Discussion

hitesh1607's avatar
hitesh1607
Advocate II
6 years ago
Solved

Average on Measure

I have a measure to calculate the Rolling Count   RollingMeasure = VAR MaxDate = MAX ( 'Date'[Date] )  RETURN CALCULATE ( COUNT(fctTable[ID]),  fctTable[HireDate] <= MaxDate,  ALL ( Date)  )...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi hitesh1607,

     

    Have you resolved it? If not, you could reference my way to have a try:

    RollingMeasure =
CALCULATE (
    SUM ( 'Table'[Values] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
            && 'Date'[Year] = MAX ( 'Date'[Year] )
    )
)

    Avg =
AVERAGEX (
    FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Year] = MAX ( 'Date'[Year] ) - 1 ),
    [RollingMeasure]
)

     

    If this can't help you, please share some sample data and expected result with us. Thanks.

     

hitesh1607's avatar
hitesh1607
Advocate II
6 years ago

I am trying to use Average on a measure to get last year's result. Not sure how to do that. This option does not allow me to use a rolling sum measure which I calculated.

I already have a rolling sum but I need to calculate average Rolling SUM for last year.

v-xuding-msft's avatar
v-xuding-msft
Community Support
6 years ago

Hi hitesh1607,

 

Have you resolved it? If not, you could reference my way to have a try:

RollingMeasure =
CALCULATE (
    SUM ( 'Table'[Values] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
            && 'Date'[Year] = MAX ( 'Date'[Year] )
    )
)

Avg =
AVERAGEX (
    FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Year] = MAX ( 'Date'[Year] ) - 1 ),
    [RollingMeasure]
)

 

If this can't help you, please share some sample data and expected result with us. Thanks.