topic Re: Group range of dates in DAX Commands and Tips

Group range of dates

Anonymous — Wed, 23 Mar 2022 17:57:45 GMT

Hello,

I have a database (simplified model below) where I have the following columns:

Machine
Date
Variable (has 3 possible values)
Value

I need to group of the range of consecutive days where the "Variable" is the same and to show min / max values.

See example below, each colour represents a new range (variable changed from 1 to 2, and from 2 to 1).

OBS: The "Index" column is not available in my database, I inserted in the Excel table for demonstration only.

Is there a possible way to do that via DAX?

Re: Group range of dates

tamerj1 — Wed, 23 Mar 2022 18:11:35 GMT

Hi Anonymous

Just create measures with simple MIN/MAX of each column

Re: Group range of dates

Anonymous — Wed, 23 Mar 2022 18:17:37 GMT

Hello tamerj1 , thanks for your time.

I should have explained better, but when I create simple measures with MIN/MAX I am going to have as an outcome the entire dataset and I need the MIN/MAX for the "Index" (range of consecutive days).

Please see picture below:

Re: Group range of dates

tamerj1 — Wed, 23 Mar 2022 18:24:43 GMT

Do you already have the index column or you want to create it?

Re: Group range of dates

Anonymous — Wed, 23 Mar 2022 18:30:06 GMT

I need to create it.

Re: Group range of dates

tamerj1 — Thu, 24 Mar 2022 03:55:32 GMT

Sorry Anonymous I just saw your reply.
Yes the index column can be created by dividing the rank over 3 (the group size) We just need to round it up to conver to ineger.

INDEX = VAR GroupSize = 3 VAR Ranking = RANKX ( CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[MACHINE] ) ), Data[DATE],, ASC ) VAR Result = ROUNDUP ( DIVIDE ( Ranking, GroupSize ), 0 ) RETURN Result

Here is a sample file for your reference https://www.dropbox.com/t/gUbW1KY31MOVROXc

Re: Group range of dates

Anonymous — Thu, 24 Mar 2022 12:28:16 GMT

Hello tamerj1 I really appreciate that you're taking your time to help me but unfornately the solution is not as expected. There is only 1 machine "A" in the example. I wrote only 3 lines per "index" but there can be as many as possible for each condition so there is no rule for that also. Any ideas?

OBS: There are several records for each machine daily...

Re: Group range of dates

tamerj1 — Thu, 24 Mar 2022 12:36:05 GMT

Hi Anonymous
Regarding the error, this code is for a calculated column not a measure.
Regarding the group size actually I anticipated that you would say that. And my question is: if not fixed then based on what?

Re: Group range of dates

Anonymous — Mon, 28 Mar 2022 07:33:24 GMT

Hi Anonymous ,

Please firstly add a Index column in Power Query:

Then create columns:

Flag = var _pre= CALCULATE(MAX('Table'[VARIABLE]),FILTER('Table',[MACHINE]=EARLIER('Table'[MACHINE]) && [Index]=EARLIER('Table'[Index])-1)) return IF([VARIABLE]<>_pre,[Index])

Filled Flag = MAXX(FILTER('Table',[MACHINE]=EARLIER('Table'[MACHINE]) && [Index]<=EARLIER('Table'[Index])),[Flag])

Rank(Grouped Index) = RANKX(FILTER('Table',[MACHINE]=EARLIER('Table'[MACHINE])),[Filled Flag],,ASC,Dense)

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Group range of dates

Anonymous — Mon, 28 Mar 2022 14:59:34 GMT

Hello!

Can you please take a look at the sample of the file, with the exact column names that I am using, I don't know why I'm not able to use the formula as you shared with me.

In the calculated columns, after the =EARLIER the errors start to appear.

MACHINE = MCH_CODE

VARIABLE = REMARK (3 options: 7TC, 7TF, 7TJ), these are the working sites, no math is involved with this column.

REG_DATE = Date + Time of the registry

RECORDED_VALUE = Value

Link below (can't attach files directly)

https://www.dropbox.com/s/0zbcjbn253vg3vs/PBI%20Sample%20FIle.pbix?dl=0

Thanks in advance for your help!