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.
I have a table (tbl_USA_Canada) with 3 columns. Date, Territory and % of Sales. I am trying to display the Territory with the % change of sales from the prior month and can't seem to get this to work.
1. I created a measure to get the prior month % of sales:
prior mo % of sales = CALCULATE([% of Sales],PREVIOUSMONTH('tbl_USA_Canada'[Date]))
2. I created a measure to calculate the % change from prior month:
% Change from prior mo = [% of sales]-[prior mo % of sales]/[prior mo % of sales]
3. I added a visual level filter for Feb'19
The result that I'm getting is not correct. Can you provide any insight as to what I am doing wrong? Thanks!
Solved! Go to Solution.
Hi,
Assuming the entries in the Date column of tbl_USA_Canada Table are proper date entries, try this
Hope this helps.
Hi, I need help in month on month rate change dax, how to get the xls or pbix posted here, and or to send to some experts
It would be something like
Month AC_grp Area F_count Currency Listed rate Remarks
01/04/22 38 AAA 1 INR 1,100
01/04/22 72 AAA 1 INR 1,000
01/04/22 73 AAA 1 INR 1,000
01/05/22 38 AAA 1 INR 1,500 Rate change
01/05/22 72 AAA 1 INR 1,600 Rate change
01/05/22 73 AAA 1 INR 1,500 Rate change
01/06/22 38 AAA 1 INR 1,500
01/06/22 72 AAA 1 INR 1,600
01/06/22 73 AAA 1 INR 1,500
Since May'22 rate is different to Apr'22 - I need a remark "Rate change, and rate for Jun'22 is same as May'22, the remark should be NIL
How can this be achieved.
Hi,
Do you want a measure or a calculated column formula? Also, within a date, will the AC_grp be in ascending order?
Hi,
Assuming the entries in the Date column of tbl_USA_Canada Table are proper date entries, try this
Hope this helps.
THANK YOU!!! This worked like a charm. I struggled with this for so long and your solution was perfect!!!
You are welcome.
@Reetz ,
Create one measure using DAX like pattern below:
% Change from prior mo = VAR Current_Month = MONTH ( tbl_USA_Canada[Date] ) VAR Current_Month_Per = CALCULATE ( MAX ( tbl_USA_Canada[% of sales] ), FILTER ( ALL ( tbl_USA_Canada ), tbl_USA_Canada[Date] = Current_Month ) ) VAR Previous_Month = Current_Month - 1 VAR Previous_Month_Per = CALCULATE ( MAX ( tbl_USA_Canada[% of sales] ), FILTER ( ALL ( tbl_USA_Canada ), tbl_USA_Canada[Date] = Previous_Month ) ) RETURN Current_Month_Per - Previous_Month_Per / Previous_Month_Per
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your response. Unfortunately, I am getting the following error :
A single value for column 'Date' in table 'tbl_USA_Canada' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
It looks like it doesn't like the DATE column but I don't know how to fix it.
I could attach the power bi file, but I don't see where I can do this.
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |