Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Count under condition

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 inc...
condition
COUNT
DAX
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    You are my hero! Thxs a lot!