Lookup Value Last Month Based on Time Generated Column

Anonymous , You have date with time stamp, what does last month mean same date and time stamp ?

example new column =

var _date= datevalue( [Time generated])

Var _check = date(year(_date), month(_date)-1, day(_date) )

return

sumx(filter(Table, [MetricName] = earlier([MetricName])  && [Time generated] = _check) ,[thisMonth_CPUAvg] ) 

Better to have date column

Date = Datevalue[Tiem Generated]

Join it with a date table

and create meausres likes

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

Date= Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
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 :radacad sqlbi My Video Series Appreciate your Kudos.

Hi amitchandak

Thank you for your reponse. I managed to create the column with the formula you provided. However, I need to match the Hour to narrow now the average on that hour. Here is what I got so far. 

When I followed your forumla it didn't take into consideration for the hour and was showing the correct number. I modified your query to include the Hour but its not working right. As you can see in the screenshot the only hour it picked up was Zero. What do you suggest I do to fix this?

Anyone able to help me with this forumla?

I can't see what I'm doing wrong. I need to include the hour in the filter to acurately determine what the Avg CPU was last month at the same hour.