average by text column

[Anonymous]

Hi, I want to calculate average by a month column, but the data type of the column is text, here is the sample data and result of cal required, do you have any solutions? Thank you.

[v-xinruzhu-msft]

Hi @Anonymous  ,

You can create a index column in power query, then close and apply

Create a new column in table

Column = AVERAGEX(FILTER('Table (3)','Table (3)'[Index]<=EARLIER('Table (3)'[Index])),[Value])

^{Best Regards,}

^{Yolo Zhu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[amitchandak · Accepted solution]

@Anonymous , Convert this month into date

date = Datevalue("01 " & [Month] & " 2022")

You can join this with date table

Calendar = Addcolumns(calendarauto() ), "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")
)

then you can have a cumulative measure

Cumm Sales = CALCULATE(AverageX(Values('Date'[Month Year]), CALCULATE(SUM(Sales[Sales Amount]))),filter(all('Date'),'Date'[date] <=max('Date'[date])))

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.