Group range of dates

[Anonymous]

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?

[Anonymous]

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.

[Anonymous]

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!

[tamerj1]

Hi @Anonymous 

Just create measures with simple MIN/MAX of each column

[Anonymous]

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:

[tamerj1]

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

[Anonymous]

I need to create it.

[tamerj1]

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

[Anonymous]

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...

[tamerj1]

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?