Forum Discussion
Average on Measure
I have a measure to calculate the Rolling Count
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.
6 Replies
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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.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.
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-TITHW/m-p/434008
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 🙂
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.
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.
