Average on Measure

[hitesh1607]

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) 

)

I need to create a new measure to get the Sum of RollingMeasure for Previous year (all the 12 months sum)

(i already have relative year field in Date table which we can use by adding filter where "RelativeYear = -1")

Also, I need an average for a complete Previous year based on RollingMeasure 

Thank you

[Fowmy]

@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]

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 · Accepted solution]

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.

[amitchandak]

@hitesh1607 , you can use time intelligence with date

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

in case you need rolling

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[hitesh1607]

I am trying to use Average on a measure to get last year's result. Not sure how to do that.

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

[Greg_Deckler]

@hitesh1607 - You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...