Lookup Value Last Month Based on Time Generated Column

[Anonymous]

Hi All, 

I'm looking for assistance with writing a new column forumula to looks up the value last month for each row. I'm a beginner with Power BI and I'm not sure how best to write the forumla for this new column. I have 6 months of data in the table. What I'm looking to achieve is 4 columns, which are MetricName, TimeGenerated, thisMonth_CPUAvg and lastMonth_CPUAvg. I tried writing the forumla myself but got multiple errors. Is there any one that provide some guidance. The first three columns was extracted from Azure for a particular service. The purpose is to show the value it was last month as the same time

[Anonymous]

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. 

AVG_CPU_LastMonth =

var _hrcheck = HOUR(TIMEVALUE(data[TimeGenerated]))

var _date = DATEVALUE(data[TimeGenerated])

var _check = DATE(YEAR(_date), MONTH(_date) -1, DAY(_date))

return

SUMX(FILTER(data, data[TimeGenerated] = _check && data[HOUR] = _hrcheck), data[thisMonth_CPU])

[Anonymous]

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?

[amitchandak]

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