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.

     

amitchandak's avatar
amitchandak
Super User
6 years ago

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.

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.

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