cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
darraghfarrell Frequent Visitor
Frequent Visitor

comparing month on month non-numerical values in a dataset

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? 

image.png

 

 

 

 

 

 

 

 

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.

Capture.PNG

 

 

 

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. 

image.png

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-yulgu-msft
Microsoft

Re: comparing month on month non-numerical values in a dataset

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"

1.png

 

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",
            ""
        )
    )

2.png

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Super User
Super User

Re: comparing month on month non-numerical values in a dataset

@darraghfarrell check this blog, hope it helps






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Super User
Super User

Re: comparing month on month non-numerical values in a dataset

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Microsoft v-yulgu-msft
Microsoft

Re: comparing month on month non-numerical values in a dataset

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"

1.png

 

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",
            ""
        )
    )

2.png

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)