Forum Discussion
DAX Count under condition
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
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.
Community ChampionAnonymous 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
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 )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.