CODEWITHSUNDEEP
powerbidax
R_Student
R_Student

Reputation: 789

Calculating percentage change over month in Power BI

I am trying to calculate the percentage change over period of one MONTH of the subtotal of defective items in my dataset which looks like this:

Date ID_REG
1/05/2002 2190
11/05/2002 13256
21/05/2002 23325
21/05/2002 12204
21/05/2002 28598
21/05/2002 14825
31/05/2002 40453
10/06/2002 26199
10/06/2002 31255
20/06/2002 4799
20/06/2002 25757
20/06/2002 44350
20/06/2002 8153
20/06/2002 45809
20/06/2002 51781
30/06/2002 14604
30/06/2002 12473
10/07/2002 12987
20/07/2002 11019
30/07/2002 15352
9/08/2002 50943
19/08/2002 41942
29/08/2002 5207
8/09/2002 188
8/09/2002 38890
8/09/2002 33634
18/09/2002 25477
18/09/2002 4941
28/09/2002 40831
28/09/2002 27395
37527 41821

Each record of ID_REG represents the SERIAL_NUMBER of a product that was deemed defective and I need to calculate the change in percentage of defective products for instance In May 2002 there were only 7 defective products in total and in April 2022 there were 10, this means a difference of 100*(10-7)/7

In order to calculate this measure I'm using the following DAX:

P_CHANGE_MONTH = 

    VAR __PREV_MONTH =
        CALCULATE(
            COUNTA('Sheet1'[ID_REG]),
            DATEADD('Sheet1'[Date].[Date], -1, MONTH)
        )
    RETURN
        DIVIDE(COUNTA('Sheet1'[ID_REG]) - __PREV_MONTH, __PREV_MONTH)

But I always get 0.0% no matter what I do, I always get 0.0% I have tried changing the date format and even the number of periods but it does not seem to work, look:

enter image description here

I want to be able to get a table like this one:

Month Subtotal Delta %
5 7 0,0%
6 10 42,9%
7 3 -70,0%
8 3 0,0%
9 8 166,7%

I have read some posts on the PBI forum, but those solutions never seem to work on my dataset.

Upvotes: 1

Views: 2466

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

You can do this in Power Query (Home=>Transform Data=>Home=>Advanced Editor)

If your query that returns the above table is named Table, then add a blank query and pasted this M code in place of the code in the window that appears when you select Advanced Editor:

let
    Source = Table,
    #"Added Custom" = Table.AddColumn(Source, "YearMonth", each Date.ToText([Date],"yyyyMM"), type text),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"YearMonth"}, {{"Defects", each Table.RowCount(_), Int64.Type}}),
    #"Shifted Count" = Table.FromColumns(
        Table.ToColumns(#"Grouped Rows") & {{null} & List.RemoveLastN(#"Grouped Rows"[Defects],1)},
        type table[YearMonth=Text.Type, Defects=Int64.Type, Shifted Defects=Int64.Type]
    ),
    #"Added Custom1" = Table.AddColumn(#"Shifted Count", "perCent Change", each ([Defects]-[Shifted Defects])/[Shifted Defects], Percentage.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Shifted Defects"})
in
    #"Removed Columns"
  • Create a column with only the YearMonth (yyyyMM)
    • I included the year in case your data spanned multiple years and you want to separate the values that way. If not, just format as MM
  • Aggregate by Count (= Defects)
  • Add a shifted column to the table
  • Add a custom column with the percent change calculation

enter image description here

You can also do this with DAX

  • Create a new table from your original data table
  • Add a "first of the month" column
  • Access the previous months value by using LOOKUPVALUE function along with DATEADD referring to the previous month.

New Table

Table 2 = 
    GROUPBY(
        ADDCOLUMNS('Table',"StartOfMonth", 1+EOMONTH('Table'[Date],-1)),
        [StartOfMonth],
        "Defects",
        COUNTAX(CURRENTGROUP(),"StartOfMonth"))

Add the Percent Change Column

Percent Change = 
    VAR prev = LOOKUPVALUE('Table 2'[Defects],'Table 2'[StartOfMonth], DATEADD('Table 2'[StartOfMonth],-1,MONTH))
RETURN
    ([Defects]-prev)/prev

Upvotes: 1

Related Questions