Finding Customer Movement from 1 segment to other

[kruzack]

Hi All,

I am pretty new to powerbi report development and i have got a problem to solve.

I have a table ordered by dates having the below 3 columns
Date, CustomerID, DPD.

Below is sample data for the same

Date	CustomerID	DPD
31-May-23	1234	0
31-May-23	2345	30
31-May-23	3456	35
01-Jun-23	1234	0
01-Jun-23	2345	0
01-Jun-23	3456	36
26-Jun-23	2345	56
26-Jun-23	3456	61

I am looking for a report which would show the total number of upgrade, downgrade and same status between 2 dates selected from the same table

Date selection would be done by the user through a slicer, and whatever dates are selected the data should be filtered only for those dates and then show a summary as below
for e.g. if i have 1000 accounts distirbuted as below as of from date

DPD Group	Total Accounts From Date	Upgrade	Same Status	Downgrade	Total Accounts To Date
Group 0	800		750	50	800
Group 1-30	150	25	100	25	150
Group 31-60	50	5	30	15	50
Total	1000	30	880	90	1000

Can you please guide me thorugh on how to acheive the desired result.

The date selection for from and to date would be from the user side.

I have daily level data for a year in the same table and any date can be selected by the user.

This is basically done to check how many accounts are upgrading and downgrading between 2 dates selected by user.

It would be a great help if you can guide me through a step by step process for the same.

[Anonymous]

HI @kruzack,

It sounds like a common multiple date fields range analysis requirement, you can take a look at the following links about start/end date analysis and dynamic segmentation if helps:

Before You Post, Read This: start, end date 

Dynamic segmentation – DAX Patterns
Regards,

Xiaoxin Sheng

[kruzack]

Hi @Anonymous 
Thanks for replying back.

I went over the post you mentioned in the comments.

It is slightly different from the problem which I am working on.

I need to check the status of the same customer over 2 different dates. So if there are 1000 customer from the date selected as of Date 1 from slicer 1, the same 1000 customers i need to track to look at their status as of Date 2 from slicer 2, and then compare how many are going up / down / same status.

I hope this might give you more details on my problem.

Thanks,

Krunal

[Anonymous · Accepted solution]

Hi @kruzack,

For your requirement, I think you need to create a segmentation table(group, from, to) and an unconnected date table.(segmentation group used as category, date table use as source of slicer)
Then you need to create two measure formulas based on the from date and to date. They should include a variable with summarize function and use date and use id as category to aggreated DPD value and you can add a custom field to remark the group based on aggregated DPD values.
After these steps, you can simply filter and aggregate the variable table records based on correspond segmentation group and compare between two measure result to get the detail group status changes.

from Rate =
VAR _startDate =
    MIN ( 'Selector'[Date] )
VAR summary =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( ALLSELECTED ( 'Table' ), [Date] = _startDate ),
            'Table'[Date],
            'Table'[CustomerID],
            "Total DPD", SUM ( 'Table'[DPD] )
        ),
        "Group",
            MAXX (
                FILTER (
                    ALLSELECTED ( Segmentation ),
                    [From] <= [Total DPD]
                        && [To] >= [Total DPD]
                ),
                [DPD Group]
            )
    )
VAR currGroup =
    SELECTEDVALUE ( Segmentation[DPD Group] )
RETURN
    COUNTROWS ( FILTER ( summary, [Group] = currGroup ) )

To Rate =
VAR _endDate =
    MAX ( 'Selector'[Date] )
VAR summary =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( ALLSELECTED ( 'Table' ), [Date] = _endDate ),
            'Table'[Date],
            'Table'[CustomerID],
            "Total DPD", SUM ( 'Table'[DPD] )
        ),
        "Group",
            MAXX (
                FILTER (
                    ALLSELECTED ( Segmentation ),
                    [From] <= [Total DPD]
                        && [To] >= [Total DPD]
                ),
                [DPD Group]
            )
    )
VAR currGroup =
    SELECTEDVALUE ( Segmentation[DPD Group] )
RETURN
    COUNTROWS ( FILTER ( summary, [Group] = currGroup ) )

Regards,

Xiaoxin Sheng