Calculate This month and Last Month Average #DATEADD

[lon0316]

I have a KPI table and within the table it calculates the actuals (a percentage in this case of plan vs actual).  I have a formula that accurately displays current month actuals for the KPI dictated by the filter.  However, I feel like it's a bit sloppy and I think I could run into troubles with it in the future.  I was thinking of using DATEADD but when I tried it, I got an average of January - April instead of just April.

This works but is messy in my opinion: 

Current Month KPI Actuals = VAR CurrentMaxDate=MAX('KPI''s Table'[Date])

VAR CurrentMonth = MONTH(CurrentMaxDate)

Return CALCULATE(AVERAGE('KPI''s Table'[Actual]),'KPI''s Table'[Month]=CurrentMonth)

I want to use Dateadd and tried this with no success:

Test Current_Month_Actual = CALCULATE([Current KPI Actuals], dateadd('Dates'[Date],-1,month))

//since we are in May I thought month-1 would return April; but instead, gives me an average of Jan-Apr

[lon0316 · Accepted solution]

I appreciate the responses however it seems people are making it more complicated than I wanted.  For this result the KPI needs two visual filters:  one for month because DATEADD returns all dates and one for the KPI name that you want to see.

Current KPI = AVERAGE('KPI''s Table'[Actual]) ** This returns your must current KPI result which in my case is April 2020

Prior KPI = CALCULATE(AVERAGE('KPI''s Table'[Actual]), DATEADD(Dates[Date],-1,month)) ** This returns the value in March

Keep in mind, my visual filter is set to April and my other visual filter is the KPI name I'm displaying

[amitchandak]

@lon0316 , This should have check date table is marked as date

Another option you can try is

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

[v-gizhi-msft]

Hi,

It is because DATEADD function will return a table that filtered by expression.

So it will not only return one month data but all months data after filtered.

If you want to cauculate the previous data, please take following steps:

1)Create a What If parameter:

Previous = GENERATESERIES(0, 10, 1)

2)Try this measure:

Measure = 
CALCULATE (
    AVERAGE ( 'Table'[Actual] ),
    FILTER (
        'Table',
        MONTH ( 'Table'[Date] )
            = MONTH ( TODAY () ) - Previous[Previous Value]
    )
)

3)When selecting one value in slicer, it shows:

See my attached pbix file.

Best Regards,

Giotto

[lon0316]

I appreciate the effort.  Thank you for letting me know DATEADD will return all dates.  I think a simple filter corrects the problem.  Your result might be useful in some cases but is much more complicated than it needs to be.

[Greg_Deckler]

@lon0316 - It's hard to tell what is going on. Is your Dates table related to your KPI's Table? Time intelligence functions are, in my opinion, a complete and utter pain to deal with and I generally try to avoid them and just use filters, because that's all they are, fitlers wrapped in a black box that are hard to understand what is really happening internally, have mediocre documentation and just generally don't work the way you would expect them to work. The funny part is that they were created to make time (well date) calculations "easier" and honestly considering the number of questions about them on the forums, failed that mission entirely. That's the other funny part, they don't actually deal with time in the sense of hours, minutes and seconds generally (some do), they really should be called "Impossible to use, black box calendar filters" in my opinion. Anyway, off my soap box...

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