Forum Discussion
Solved
DAX Count consecutive
Hi, How do I write DAX to calculate these? I'd like to achieve the Total column number. Thank you,
Hi,
PBI file attached.
Hope this helps.
hello topazz11
please check if this accomodate your need.
1. Unpivot your table and change null value into 0
2. create a calculated column to seek min value of consecutive date
Min Date =
var _Date =
ADDCOLUMNS(
ADDCOLUMNS(
'Table',
"Next Date",
MAXX(
FILTER(
'Table',
'Table'[Item]=EARLIER('Table'[Item])&&
'Table'[Date]<EARLIER('Table'[Date])
),
'Table'[Date]
)
),
"ConsDate",
MAXX(
FILTER(
'Table',
'Table'[Item]=EARLIER('Table'[Item])&&
'Table'[Date]=[Next Date]
),
'Table'[Value]
)
)
Return
MAXX(
FILTER(
_Date,
'Table'[Item]=EARLIER('Table'[Item])&&
'Table'[Value]=1&&
[ConsDate]=0
),
'Table'[Date]
)
3. create a calculated column to seek max date of consecutive date (this has more syntax since you want the max value even it is not consecutive date for example in Item B at 10-Jan)
Max Date =
var _Date =
ADDCOLUMNS(
ADDCOLUMNS(
ADDCOLUMNS(
ADDCOLUMNS(
'Table',
"Next Date",
MAXX(
FILTER(
'Table',
'Table'[Item]=EARLIER('Table'[Item])&&
'Table'[Date]<EARLIER('Table'[Date])
),
'Table'[Date]
)
),
"ConsDate",
MAXX(
FILTER(
'Table',
'Table'[Item]=EARLIER('Table'[Item])&&
'Table'[Date]=[Next Date]
),
'Table'[Value]
)
),
"IFMax",
var _Max =
MAXX(
FILTER(
'Table',
'Table'[Item]=EARLIER('Table'[Item])&&
'Table'[Value]=1&&
[ConsDate]=1
),
'Table'[Date]
)
Return
IF(
'Table'[Min Date]='Table'[Date],
'Table'[Date],
_Max
)),
"MaxDate",
MAXX(
FILTER(
'Table',
'Table'[Item]=EARLIER('Table'[Item])
),
[IFMax]
)
)
Return
MAXX(
FILTER(
_Date,
'Table'[Item]=EARLIER('Table'[Item])
),
[MaxDate]
)
4. create a calculated column to calculate datediff between min and max date.
Datediff =
DATEDIFF(
'Table'[Min Date],
'Table'[Max Date],
DAY
)+1
this is quite messy but hopefully you can get the idea where to start.
Hope this will help.
Thank you.