Forum Discussion
Post PatronHow to reset count where yyyymm Date gaps exist
How can I add a condition to the below dax to identify the gap between 202209 and 202305 and reset the count?
-- Calc Column
Active_records =
var _GP = Table[GP]
var indv_id = Table[ID]
var _end_date = Table[DATE]
var _DATE = EOMONTH(_end_date,-24)+1
VAR _COUNT = RANKX (
FILTER (ALL(Table),
Table[GP] = EARLIER(Table[GP])
&& Table[ID] = EARLIER(Table[ID])
&& Table[MALES_UNDER_35] = EARLIER(Table[MALES_UNDER_35])
&& Table[DATE] >= _DATE
&& Table[DATE] <= _end_date
),
Table[DATE],
,
ASC,
Dense
)
RETURN
_COUNT
I ended up adding new field in sql using lag function then added to my calculated column!
9 Replies
- Greg_Deckler
Community Champion
NilR Seems like a variation on Cthulhu. Cthulhu - Microsoft Fabric Community
NilRI ended up adding flag to my data with sql. Thanks!
- technolog
Super User
I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.
Your insights and updates will greatly assist others who might be encountering the same challenge.
- NilR
I could not figure it out!
- technolog
GP YYYYMM DATE Active_records Desired Result 33 202105 5/1/21 1 1 33 202106 6/1/21 2 2 33 202107 7/1/21 3 3 33 202108 8/1/21 4 4 33 202109 9/1/21 5 5 33 202110 10/1/21 6 6 33 202111 11/1/21 7 7 33 202112 12/1/21 8 8 33 202201 1/1/22 9 9 33 202202 2/1/22 10 10 33 202203 3/1/22 11 11 33 202204 4/1/22 12 12 33 202205 5/1/22 13 13 33 202206 6/1/22 14 14 33 202207 7/1/22 15 15 33 202208 8/1/22 16 16 33 202209 9/1/22 17 17 33 202305 5/1/23 17 1 33 202306 6/1/23 17 2 33 202307 7/1/23 17 3 Desired Result = RANKX( FILTER( ALL('Table'), 'Table'[GP] = EARLIER('Table'[GP]) && 'Table'[YYYYMM] <= EARLIER('Table'[YYYYMM]) ), 'Table'[YYYYMM], , ASC, Dense )
