Forum Discussion
average by text column
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.
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.
2 Replies
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.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.