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.

     

Fowmy's avatar
Fowmy
Super User
6 years ago

hitesh1607 

Try the Quick Measures: You change the Function within the measure to SUMEX and son on

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply šŸ™‚

YouTube, LinkedIn

 

 

  • 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.