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.
Hello All,
I am looking for some assistance in finding a calculated column which gives me results as specified in column Sales at Last date(Previous Month)
I have used this DAX and it pulls the latest value instead of last value from last month date
Date | Product | Sales | Last date of Month | Sales at Last date(Previous Month) |
3/22/2022 | Cycle | 185 | 2/28/2022 | |
3/23/2022 | Cycle | 118 | 2/28/2022 | |
3/24/2022 | Cycle | 153 | 2/28/2022 | |
3/25/2022 | Cycle | 103 | 2/28/2022 | |
3/26/2022 | Cycle | 145 | 2/28/2022 | |
3/27/2022 | Cycle | 189 | 2/28/2022 | |
3/28/2022 | Cycle | 183 | 2/28/2022 | |
3/29/2022 | Cycle | 115 | 2/28/2022 | |
3/30/2022 | Cycle | 194 | 2/28/2022 | |
3/31/2022 | Cycle | 105 | 2/28/2022 | |
4/1/2022 | Cycle | 197 | 3/31/2022 | 105 |
4/2/2022 | Cycle | 190 | 3/31/2022 | 105 |
4/3/2022 | Cycle | 105 | 3/31/2022 | 105 |
4/4/2022 | Cycle | 119 | 3/31/2022 | 105 |
4/5/2022 | Cycle | 152 | 3/31/2022 | 105 |
4/6/2022 | Cycle | 156 | 3/31/2022 | 105 |
4/7/2022 | Cycle | 134 | 3/31/2022 | 105 |
4/8/2022 | Cycle | 126 | 3/31/2022 | 105 |
4/9/2022 | Cycle | 144 | 3/31/2022 | 105 |
4/10/2022 | Cycle | 195 | 3/31/2022 | 105 |
4/11/2022 | Cycle | 141 | 3/31/2022 | 105 |
4/12/2022 | Cycle | 169 | 3/31/2022 | 105 |
4/13/2022 | Cycle | 127 | 3/31/2022 | 105 |
4/14/2022 | Cycle | 174 | 3/31/2022 | 105 |
4/15/2022 | Cycle | 120 | 3/31/2022 | 105 |
4/16/2022 | Cycle | 156 | 3/31/2022 | 105 |
4/17/2022 | Cycle | 120 | 3/31/2022 | 105 |
4/18/2022 | Cycle | 111 | 3/31/2022 | 105 |
4/19/2022 | Cycle | 164 | 3/31/2022 | 105 |
4/20/2022 | Cycle | 160 | 3/31/2022 | 105 |
4/21/2022 | Cycle | 148 | 3/31/2022 | 105 |
4/22/2022 | Cycle | 109 | 3/31/2022 | 105 |
4/23/2022 | Cycle | 147 | 3/31/2022 | 105 |
4/24/2022 | Cycle | 118 | 3/31/2022 | 105 |
4/25/2022 | Cycle | 161 | 3/31/2022 | 105 |
4/26/2022 | Cycle | 181 | 3/31/2022 | 105 |
4/27/2022 | Cycle | 122 | 3/31/2022 | 105 |
4/28/2022 | Cycle | 189 | 3/31/2022 | 105 |
4/29/2022 | Cycle | 174 | 3/31/2022 | 105 |
4/30/2022 | Cycle | 157 | 3/31/2022 | 105 |
5/1/2022 | Cycle | 171 | 4/30/2022 | 157 |
5/2/2022 | Cycle | 142 | 4/30/2022 | 157 |
5/3/2022 | Cycle | 174 | 4/30/2022 | 157 |
5/4/2022 | Cycle | 196 | 4/30/2022 | 157 |
5/5/2022 | Cycle | 119 | 4/30/2022 | 157 |
5/6/2022 | Cycle | 188 | 4/30/2022 | 157 |
5/7/2022 | Cycle | 185 | 4/30/2022 | 157 |
5/8/2022 | Cycle | 116 | 4/30/2022 | 157 |
5/9/2022 | Cycle | 187 | 4/30/2022 | 157 |
5/10/2022 | Cycle | 160 | 4/30/2022 | 157 |
5/11/2022 | Cycle | 142 | 4/30/2022 | 157 |
Thanks all in advance and looking for some assistance here
Solved! Go to Solution.
pms = // calc column
var LastDateOfMonth = 'Data'[Last date of month]
var CurrentProduct = 'Data'[Product]
var Result =
MAXX(
filter(
'Data',
'Data'[Date] = LastDateOfMonth
&&
'Data'[Product] = CurrentProduct
),
'Data'[Sales]
)
return
Result
pms = // calc column
var LastDateOfMonth = 'Data'[Last date of month]
var CurrentProduct = 'Data'[Product]
var Result =
MAXX(
filter(
'Data',
'Data'[Date] = LastDateOfMonth
&&
'Data'[Product] = CurrentProduct
),
'Data'[Sales]
)
return
Result
Thank you so very much it worked like a charm..
need to get some experience on writing loops then..
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 |
---|---|
39 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |