Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 it gives as a value and I would like to use it as column for more calculations. Any help would be greatly appretiated.
Date | Sales | Max of Last Month | Min of Last Month |
1/1/2022 | 110 | ||
1/2/2022 | 190 | ||
1/3/2022 | 138 | ||
1/4/2022 | 153 | ||
1/5/2022 | 136 | ||
1/6/2022 | 127 | ||
1/7/2022 | 138 | ||
1/8/2022 | 101 | ||
1/9/2022 | 192 | ||
1/10/2022 | 138 | ||
1/11/2022 | 104 | ||
1/12/2022 | 115 | ||
1/13/2022 | 148 | ||
1/14/2022 | 104 | ||
1/15/2022 | 184 | ||
1/16/2022 | 167 | ||
1/17/2022 | 144 | ||
1/18/2022 | 161 | ||
1/19/2022 | 154 | ||
1/20/2022 | 194 | ||
1/21/2022 | 159 | ||
1/22/2022 | 123 | ||
1/23/2022 | 173 | ||
1/24/2022 | 194 | ||
1/25/2022 | 108 | ||
1/26/2022 | 134 | ||
1/27/2022 | 135 | ||
1/28/2022 | 138 | ||
1/29/2022 | 169 | ||
1/30/2022 | 111 | ||
1/31/2022 | 177 | ||
2/1/2022 | 122 | 194 | 101 |
2/2/2022 | 192 | 194 | 101 |
2/3/2022 | 197 | 194 | 101 |
2/4/2022 | 198 | 194 | 101 |
2/5/2022 | 114 | 194 | 101 |
2/6/2022 | 156 | 194 | 101 |
2/7/2022 | 175 | 194 | 101 |
2/8/2022 | 130 | 194 | 101 |
2/9/2022 | 141 | 194 | 101 |
2/10/2022 | 104 | 194 | 101 |
2/11/2022 | 132 | 194 | 101 |
2/12/2022 | 170 | 194 | 101 |
2/13/2022 | 146 | 194 | 101 |
2/14/2022 | 116 | 194 | 101 |
2/15/2022 | 122 | 194 | 101 |
2/16/2022 | 191 | 194 | 101 |
2/17/2022 | 167 | 194 | 101 |
2/18/2022 | 160 | 194 | 101 |
2/19/2022 | 134 | 194 | 101 |
2/20/2022 | 157 | 194 | 101 |
2/21/2022 | 112 | 194 | 101 |
2/22/2022 | 179 | 194 | 101 |
2/23/2022 | 118 | 194 | 101 |
2/24/2022 | 125 | 194 | 101 |
2/25/2022 | 128 | 194 | 101 |
2/26/2022 | 117 | 194 | 101 |
2/27/2022 | 179 | 194 | 101 |
2/28/2022 | 104 | 194 | 101 |
Solved! Go to Solution.
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,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
You are welcome! Would you be able to mark my reply as solved? Thanks!!
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,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi:
Here's the file (ignore yesterday qty table). I added date table, fyi.
https://drive.google.com/file/d/1clTMZ_Uasovmi_W9cxuD9RDBsvGrgfck/view?usp=sharing
Hi:
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"
Thank you very much Whitewater, I was able to create the colum as required.
You are welcome! Would you be able to mark my reply as solved? Thanks!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
48 | |
24 | |
20 | |
14 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |