comparing month on month non-numerical values in a dataset

[darraghfarrell]

Hi all,

I am trying to compare this months billing values with last months billing values. So the question i am trying to answer is "How many new customers do we have this month compared to last?". What is the best approach here to make it as dynamic as possible, i.e. to make it rolling month on month? 

I am comparing the Billing Month field and has 2 months worth of data, i.e. Feb and March. Ideally what I want my answer to display is the 2 new customers, as like below and highlighted above. These are the 2 new customers.

Once I have this I would also like to see which customers we have lost between March and Feb i.e. as they existed in Feb but not in March. 

[v-yulgu-msft · Accepted solution]

Hi @darraghfarrell ,

Open Query Editor, apply below changes to source table.

1. Sort [Billing Month] ascending.
2. Group by table based on [Billing Month].
3. Add index column.

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data 2.xlsx"), null, true),
    Sample_2_Sheet = Source{[Item="Sample_2",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sample_2_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Billing Month", type date}, {"Customer name", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Billing Month", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Billing Month"}, {{"all data", each _, type table [Billing Month=date, Customer name=text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Expanded all data" = Table.ExpandTableColumn(#"Added Index", "all data", {"Customer name"}, {"all data.Customer name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded all data",{{"all data.Customer name", "Customer name"}})
in
    #"Renamed Columns"

In Data view mode, create a calculated column.

Lost/New this month =
VAR isFindnextMonth =
    IF (
        LOOKUPVALUE (
            Sample_2[Customer name],
            Sample_2[Customer name], Sample_2[Customer name],
            Sample_2[Index], Sample_2[Index] + 1
        )
            = BLANK (),
        0,
        1
    )
VAR isFindpreMonth =
    IF (
        LOOKUPVALUE (
            Sample_2[Customer name],
            Sample_2[Customer name], Sample_2[Customer name],
            Sample_2[Index], Sample_2[Index] - 1
        )
            = BLANK (),
        0,
        1
    )
RETURN
    IF (
        Sample_2[Billing Month] <> MIN ( Sample_2[Billing Month] )
            && isFindpreMonth = 0,
        "New",
        IF (
            Sample_2[Billing Month] <> MAX ( Sample_2[Billing Month] )
                && isFindnextMonth = 0,
            "Lost",
            ""
        )
    )

Best regards,

Yuliana Gu

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[parry2k]

@darraghfarrell check this blog, hope it helps