topic How to reset count where yyyymm Date gaps exist in DAX Commands and Tips

How to reset count where yyyymm Date gaps exist

NilR — Mon, 28 Aug 2023 12:09:30 GMT

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

Re: How to reset count where yyyymm Date gaps exist

Greg_Deckler — Mon, 28 Aug 2023 13:07:03 GMT

NilR Seems like a variation on Cthulhu. Cthulhu - Microsoft Fabric Community

Re: How to reset count where yyyymm Date gaps exist

NilR — Wed, 30 Aug 2023 14:10:10 GMT

I ended up adding flag to my data with sql. Thanks!

Re: How to reset count where yyyymm Date gaps exist

technolog — Sun, 24 Sep 2023 08:12:07 GMT

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.

Re: How to reset count where yyyymm Date gaps exist

NilR — Mon, 25 Sep 2023 16:20:35 GMT

I could not figure it out!

Re: How to reset count where yyyymm Date gaps exist

technolog — Mon, 25 Sep 2023 17:40:52 GMT

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 )`

Re: How to reset count where yyyymm Date gaps exist

technolog — Mon, 25 Sep 2023 17:42:25 GMT

Desired Result = 
RANKX(
    FILTER(
        ALL('Table'),
        'Table'[GP] = EARLIER('Table'[GP]) && 
        'Table'[YYYYMM] <= EARLIER('Table'[YYYYMM])
    ),
    'Table'[YYYYMM],
    ,
    ASC,
    Dense
)

Re: How to reset count where yyyymm Date gaps exist

lbendlin — Tue, 26 Sep 2023 19:51:53 GMT

What's wrong with using GroupKind.Local in Power Query?

Re: How to reset count where yyyymm Date gaps exist

NilR — Thu, 28 Sep 2023 22:39:10 GMT

there is more into the actual data and it didn't work!

Re: How to reset count where yyyymm Date gaps exist

NilR — Thu, 28 Sep 2023 22:42:58 GMT

I ended up adding new field in sql using lag function then added to my calculated column!