Forum Discussion

Reetz's avatar
Reetz
Icon for Helper II rankHelper II
7 years ago
Solved

% change from prior month

 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 wor...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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.

v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
    Reetz
    Icon for Helper II rankHelper II
    7 years ago

    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.