Display specific data in matrix

[Razwan]

Hello,

I am using the following simple data model:

The FactTable has the following structure:

The Calendar table contains dates from 2018 until 2021.

In PowerBi canvas I have created the following matrix with Year filter coming from the Calendar table.

The hierarchy is Customer Name -> Division -> Sub-division

My simple measures used in the matrix are:

 Total = SUM(FactTable[Total]) + 0

Total LY = 
CALCULATE(
    [# Total],
    DATEADD('Calendar'[Date], -1, YEAR)
)

My goal is to show and count, as simple as possible, the customers who:

• have a value in the current year but not in previous year (let's call them new);
• have a value in the previous year but not in current year (let's call them lost); 

As you notice from the matrix (see the picture below) there are 4 new customers for 2020 (red) and only 2 lost customers for 2021 (blue) :

I have created additional measures to count the new and lost customers:

# New = 
CALCULATE(
    [# CountOfCust],
    FILTER(
        SUMMARIZE(
            FactTable,
            FactTable[Customer Name],
            'Calendar'[Year]
        ),
        [Total] <> 0 && [Total LY] = 0
    )
)
# Lost = 
CALCULATE(
    [# CountOfCust],
    FILTER(
        SUMMARIZE(
            FactTable,
            FactTable[Customer Name],
            'Calendar'[Year]
        ),
        [Total] = 0 && [Total LY] <> 0
    )
)

The result:

The New measure shows the correct values but the Lost doesn't (it should show 2). I am unable to take into account all the years from Calendar table regardless of the fact that Customer C and Customer D have only data for 2020.
I have also tried with SUMMARIZECOLUMNS ('FactTable'[Customer Name], 'Calendar'[Year]) in order to take all the years into account but when filtering on a year it gives an error.
So my expected behavior is whenever selecting a year, it shows the correct new and lost customers for that year.

How can I achieve this behavior?

Thank you in advance!

[Jihwan_Kim]

Hi, @Razwan 

Please correct me if I wrongly understood your question.

When seeing your screenshot, I am not sure which year's new customers and lost customers you want to count.

In my opinion, the year selection and filter in each measure need to be adjusted.

If it is OK with you, please share your sample pbix file's link here, then I can try to have a look into it.

Thanks.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Razwan]

Hello, @Jihwan_Kim 
Unfortunatelly I have no permissions to attach any file.
And to answer your question, it doesn't matter which year you select. By default it should count for all the years, then filter based on your selected year.
For example:
Given this hierarchy:
Customer X:
  ---- Division A
         ---- SubDiv A1
  ---- Division B
         ---- SubDiv B1
         ---- SubDiv B2
On top level (Customer X) has sales for 2018, 2019 and 2021.
So in a matrix/table for Customer X in:
- 2018: it will show new (logically because it's the first sales year);
- 2019: it will show nothing because it has sales for current year and previous year;
- 2020: it will show lost (because it has sales for previous year but not for current year);
- 2021: it will show new (because it has sales for current year but not previous);
All this information should be shown in a matrix/table respecting that logic + hierarchy.
Also, count the number of the customers at the top level.
The request is really weird and I am aware of it, and my head is dizzy because I am not able to solve it....

[v-deddai1-msft · Accepted solution]

Hi @Razwan ,

Would you please try to use the following measure:

# Lost =
COUNTX (
    FILTER (
        SUMMARIZE (
            FactTable,
            FactTable[Customer Name],
            'Calendar'[Year],
            "_Total", [Total],
            "_TotalLY", [Total Ly]
        ),
        [Total] = 0
            && [Total LY] <> 0
    ),
    FactTable[Customer Name]
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[amitchandak]

@Razwan , Refer to my blog, This is on month on Month

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

You can replace datesmtd to datesytd for year and try