Forum Discussion
Solved
Help needed to create Calculated Column in finding Max\Min\Average for Last Month
Dear PowerBians I would need some assistance in finding a solution on how to implement below using Calculated Column. I was able to create using Calulate([Sales],PREVIOUSMONTH([Date])) however i...
I opt for non-context-transition func, typically date/time func instead of Time Intelligent funcs in a calculated column in order to avoid "undermining" intrinsic row context or unnecessary filter context altering.
Min LM = VAR __eolm = EOMONTH( EDATE( DATA[Date], -1 ), 0 ) RETURN MINX( FILTER( DATA, EOMONTH( DATA[Date], 0 ) = __eolm ), DATA[Sales] )Excel worksheet formula is powerful enough,
You are welcome! Would you be able to mark my reply as solved? Thanks!!
Whitewater100
Solution Sage
Solution SageHi:
You can first add a calc col for month.(could be a variable) If it's a mutli-year observation then add calc col for month-yr. I am just adding the month in so you can see.
*I'm calling your table ,"Table"
Month = MONTH('Table'[Date])
Min of L Month =
CALCULATE(MIN('Table'[Sales]),PREVIOUSMONTH('Table'[Date]),ALLEXCEPT('Table','Table'[Month]))
Max of L Month =
CALCULATE(Max('Table'[Sales]),PREVIOUSMONTH('Table'[Date]),ALLEXCEPT('Table','Table'[Month]))
I hope this is what you need..
Thank you very much Whitewater, I was able to create the colum as required.
- Whitewater100
You are welcome! Would you be able to mark my reply as solved? Thanks!!