% change from prior month

[Reetz]

 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!

[SrikanthDXB]

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.

[Ashish_Mathur]

Hi,

Do you want a measure or a calculated column formula?  Also, within a date, will the AC_grp be in ascending order?

[Nishantjain]

The right way to do this is to have a date table. Time intelligence functions works best when you have date tables

[Ashish_Mathur · Accepted solution]

Hi,

Assuming the entries in the Date column of tbl_USA_Canada Table are proper date entries, try this

1. Create a Calendar Table
2. Enter these calculated column formulas in the Calendar Table - Year = YEAR(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm")
3. Build a relationship from the Date column of the tbl_USA_Canada Table to the Date column of the Calendar Table
4. In your visual, drag Year and Month from the Calendar Table
5. Write these measures - Percent of Sales = SUM(Data[% of slaes]) and Percent of Sales in previous month = CALCULATE([Percent of Sales],PREVIOUSMONTH(Calendar[Date]) and Delta = IFERROR([Percent of Sales]/[Percent of Sales in previous moths]-1,BLANK())

Hope this helps.

[Reetz]

THANK YOU!!!  This worked like a charm.  I struggled with this for so long and your solution was perfect!!!

[Ashish_Mathur]

You are welcome.

[Nishantjain]

Great please mark it as a solution to help other users

[v-yuta-msft]

@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.

[Reetz]

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.