Rate of change from previous month

[joshcomputer1]

I am trying to show the % of change from one month to the next.  The table is SCCDDATA and the column is [cinum]. I have a count of each cinum called [cinum count].  I am using a date table named 'date'[date].  I want to show this in a table with the cinum as the rows and the month as the columns. Displaying the difference from the previous month. Any help is appreciated.

[v-yulgu-msft · Accepted solution]

Hi @joshcomputer1,

Suppose your table structure is like:

Please create a calculated table using below formula:

Summarize table =
SUMMARIZE (
    SCCDDATA,
    SCCDDATA[Date].[MonthNo],
    SCCDDATA[cinum],
    "total sales", SUM ( SCCDDATA[sales] )
)

Add a calculated column:

Diff =
'Summarize table'[total sales]
    - LOOKUPVALUE (
        'Summarize table'[total sales],
        'Summarize table'[cinum], 'Summarize table'[cinum],
        'Summarize table'[MonthNo], 'Summarize table'[MonthNo] - 1
    )

Use a matrix to display data.

Best regards,
Yuliana Gu

[joshcomputer1]

I created the summarized table as you described above. I am not able to enter SCCDDATA[Date].[MonthNo].  So I entered SCCDDATA[Date]. Any idea why I can't do that? 

Summarize table =
SUMMARIZE (
SCCDDATA,
SCCDDATA[Date],
SCCDDATA[cinum],
"CI Total", count ( SCCDDATA[cinum] )
)

I think this is causing issues on the Month over Month formula.  I have this, but get an error when I include .[MonthNo] on the last line. Obviously, without this, it doesn't slice by month. Any help is appreciated. 

Month Over Month =
'Summarize table'[CI Total]
- LOOKUPVALUE (
'Summarize table'[CI Total],
'Summarize table'[cinum], 'Summarize table'[cinum],
'Summarize table'[Date], 'Summarize table'[Date] - 1
)

[v-yulgu-msft]

Hi @joshcomputer1,

I guess you linked 'SCCDDATA' with a date dimension table based on [Date] field. In that case, please try this:

Summarize table =
SUMMARIZE (
    SCCDDATA,
    MONTH(SCCDDATA[Date]),
    SCCDDATA[cinum],
    "cinum count", COUNT ( SCCDDATA[cinum] )
)

Regards,
Yuliana Gu

[joshcomputer1]

I sent you a private message with file path. Please review. 

[joshcomputer1]

Thanks v-yulgu-msft,

The original table doesn't have a count of each cinum.  There are many columns in this table and the cinum column is all text entries.  That's why I am using ci count just to get a count of each type of cinum.  Does that make a difference in the formula you presented?

cinum     month

a                5

b                5 

c                6

d                7

a                5 

c                8

etc...

[Ashish_Mathur]

Hi @joshcomputer1,

Share a sample dataset and show your expected result.

[v-yulgu-msft]

Hi @joshcomputer1,

In that case, you should modify the formula for calculated table as below:

Summarize table =
SUMMARIZE (
    SCCDDATA,
    SCCDDATA[Date].[MonthNo],
    SCCDDATA[cinum],
    "cinum count", COUNT ( SCCDDATA[cinum] )
)

The rest steps are the same.

Regards,
Yuliana Gu