DAX Count under condition

[Anonymous]

Hey everyone,

I have a DAX question for you. I have a data set that contains settled income of people on benefits. This income is recorded monthly by date.

To see which people have received new income, I would like to make a chart in which you can see per month for how many people (unique) income has been settled. PLEASE NOTE that no income has been settled in the past 3 months.

My dataset contains 3 columns:

1) ID (unique personal number).

2) DATE (Start date of settled earnings).

3) KIND (Type of income).

I already have a calendar table, so I think I could achieve this with a DAX calculation.

Who can and wants to help me? My experience with DAX is limited.

[smpa01]

@Anonymous  provide sample data and expected output / sample pbix and expected output?

[Anonymous]

Hi, the link below will take you to 2 files. An Excel with data and a .pbix.

I have already loaded the data into the .pbix. In addition, I made a graph with the value of a unique count on ID (each unique ID stands for a person) and on the Axis the DATE (this is a date on which someone's income has been settled).

Now what I want is to add a calculated column to the table. This column should state whether the person (ID) has already had income in the past 3 months prior to the DATE. If yes then 0 and if no then 1.

In this way I can create a chart that only includes the persons (ID) who had income in a month, if they had no income in the three months prior to that date.

I hope you understand what I mean.

https://8ktd365-my.sharepoint.com/:f:/g/personal/j_brockhus_8ktd365_nl/EpJKNjHxE6BOokW9SiSZSMgBe7Bg8...

[smpa01]

@Anonymous  you can use a measure like this which would give you this

UniqueIDCount/MO =
VAR _count1 =
    DISTINCTCOUNT ( 'fact'[ID] )
VAR _rank =
    RANKX (
        ALLSELECTED ( 'Calendar'[Year-Month] ),
        CALCULATE ( MAX ( 'Calendar'[Year-Month] ) ),
        ,
        ASC,
        DENSE
    )
VAR _mxMO =
    CALCULATE ( MAX ( 'Calendar'[Date] ), 'fact' )
VAR _count =
    EXCEPT (
        SUMMARIZE ( FILTER ( ALL ( 'fact' ), 'fact'[DATE] <= _mxMO ), 'fact'[ID] ),
        VALUES ( 'fact'[ID] )
    )
VAR _count2 =
    COUNTROWS ( _count )
VAR _x =
    IF ( _rank = 1, _count1, _count2 )
RETURN
    _x

[Anonymous]

Thank you for your response!

Unfortunately, this still does not give the desired result. Two points:

1) He now calculates a cumulative. I would like to count the unique number of people who had income in a given month, while they had no income in the previous three months. And that every month again.

2) He is not looking back 3 months now.

[smpa01]

@Anonymous 

custCountNoIncomeLast3Months =
VAR _max =
    CALCULATE ( MAX ( 'Calendar'[Year-Month] ), 'fact' )
VAR _filt =
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year-Month] <= _max )
VAR _minYRMO =
    MINX (
        TOPN (
            4,
            SUMMARIZE ( _filt, 'Calendar'[Year-Month] ),
            'Calendar'[Year-Month], DESC
        ),
        'Calendar'[Year-Month]
    )
VAR _maxYRMO =
    MINX (
        TOPN (
            2,
            TOPN (
                3,
                SUMMARIZE ( _filt, 'Calendar'[Year-Month] ),
                'Calendar'[Year-Month], DESC
            ),
            'Calendar'[Year-Month], DESC
        ),
        'Calendar'[Year-Month]
    )
VAR _minYRMO1 =
    CALCULATE ( MIN ( 'Calendar'[Date] ), 'Calendar'[Year-Month] = _minYRMO )
VAR _maxYRMO1 =
    CALCULATE ( MAX ( 'Calendar'[Date] ), 'Calendar'[Year-Month] = _maxYRMO )
VAR _except1 =
    EXCEPT (
        VALUES ( 'fact'[ID] ),
        SUMMARIZE (
            FILTER (
                ALL ( 'fact' ),
                'fact'[DATE] >= _minYRMO1
                    && 'fact'[DATE] <= _maxYRMO1
            ),
            'fact'[ID]
        )
    )
RETURN
    COUNTROWS ( _except1 )

[Anonymous]

Thanks again!

Unfortunately, I'm not quite there yet. Maybe it's just me, but I can't get your formula to work.

Isn't it possible to just create a calculated column, which checks per line whether the person in question also occurs in the past 3 months, prior to the date in the relevant line? If yes, then 1 and if no, then 0? So without using a calendar?

The formula as it stands now seems more complicated than necessary (and I can't get it to work here)

I feel almost weighed down, but would really appreciate it if someone would take another look at it.

For clarification I made an Excel file (Sample Data 2.xlsx). It states:

Column A = The customer number

Column B = Date on which income was settled

Column C = This should become the calculated column in DAX. It must therefore be checked here whether the customer has already had settled income in the past 3 months prior to the settled income. If yes, then Yes (or 1) and if no, then No (or 0).

In column D I have added as an example why you should read "Yes" or "No" in column C.

Sample Data 2.xlsx

[v-shex-msft · Accepted solution]

Hi @Anonymous,

Perhaps you can try to use the following calculated column formula to check the records based on client group and date range conditions to remark the suitable records:

InCome? = 
VAR result =
    COUNTROWS (
        FILTER (
            'T2',
            VAR currClient =
                EARLIER ( T2[Client] )
            VAR currDate =
                EARLIER ( T2[Date] )
            RETURN
                [Client] = currClient
                    && [Date]
                        > DATE ( YEAR ( currDate ), MONTH ( currDate ) - 3, DAY ( currDate ) )
                    && [Date] < currDate
        )
    )
RETURN
    IF ( result > 0, "No", "Yes" )

Regards,

Xiaoxin Sheng

[Anonymous]

You are my hero! Thxs a lot!