Forum Discussion

AMONT's avatar
AMONT
Frequent Visitor
1 year ago
Solved

Filtering Consecutive Dates by a Measure

Apologies if this has been posted before, as I do see a lot of consecutive date topics here.    I have groundwater well data that has been assigned a 1 or a 0, depending on if certain parameters ar...
Need Help
  • Irwan's avatar
    Irwan
    1 year ago

    hello AMONT 

     

    Glad it is worked as intended.

     

    if you need to limit certain id, you can add more condition inside the filter.

    for example: 

    MAXX(
        FILTER(
            ALL('Table'),
            'Table'[DateOnly]<_Select&&
            'Table'[Well_ID]=_ID *** adding at top var _ID = SELECTEDVALUE('Table'[Well_ID])***
        ),
        'Table'[DateOnly]
    )

    or directly use should have same result : 

    MAXX(
        FILTER(
            ALL('Table'),
            'Table'[DateOnly]<_Select&&
            'Table'[Well_ID]=SELECTEDVALUE('Table'[Well_ID])
        ),
        'Table'[DateOnly]
    )

    This way, the measure only calculate for previous date AND for same ID.

    you can add more filter condition as you need as above.

     

    Hope this will help.
    Thank you.

Irwan's avatar
Irwan
Super User
1 year ago

hello AMONT 

 

here is the tweak if [Total Days With 3 Exceed Ref] as a measure.

you can continue this below step from creating measure for [Total Days With 3 Exceed Ref].

Right side of above screenshot is using [Total Days With 3 Exceed Ref] as a measure which has exact same result as using calculated column in left side.

As measure works in filter context, so you need to have 'DateOnly' column as filter or else you will have blank because there is no filter.

 

 

1. create a new measure for calculating end of conscutive date

Measure Changed Value =
var _Select = SELECTEDVALUE('Table'[DateOnly])
var _Date =
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[DateOnly]<_Select
    ),
    'Table'[DateOnly]
)
var _Value = 
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[DateOnly]=_Date
    ),
    [Measure Total Days With 3 Exceed Ref]
)
Return
IF(
    [Measure Total Days With 3 Exceed Ref]<>_Value,
    1,
    0
)
2. create a new measure for calculating the first date of consecutive date
Measure Min DateOnly =
MINX(
    FILTER(
        ALL('Table'),
        'Table'[DateOnly]>=SELECTEDVALUE('Table'[DateOnly])&&
        [Measure Changed Value]=1&&
        [Measure Total Days With 3 Exceed Ref]=1
    ),
    'Table'[DateOnly]
)
3. create a new measure to define duplicate because we dont want to see many same value in between consecutive date
Measure Remove Duplicate =
IF(
    [Measure Min DateOnly]=SELECTEDVALUE('Table'[DateOnly]),
    1,
    0
)
4. Plot the mindate and nextdate of consecutive date then put the removeduplicate measure as visual filter

5. create a new measure for calculating number of consecutive date.

Measure Count =
DATEDIFF(
    [Measure Min DateOnly],
    [Measure Next Date],
    DAY
)
 
Detail is in attached pbix.
 
Hope this will help.
Thank you.
AMONT's avatar
AMONT
Frequent Visitor
1 year ago

First off, I appreciate all the time you've put into this. I owe you big time! I know this is so close to correct, but I have no idea what I'm doing wrong. 

 

The [Measure Total Days With 3 Exceed Ref] doesn't work in my pbix. I cannot perform the SUM measure since it's not a column in the table, just a measure based on several other measures as described above. As such, I get a weird result in all the other formulas that you provided.

 

I think I need a measure to mimic your measure, but in the summation of the [Total Days With 3 Exceed Ref] measure that I have. In the Card that I added, it defaults to the sum, so I guess I need that in a measure to make the same result?

 

One thing to make sure is clear, this is a single well (Well 100) example. I have over 100 wells. So I am assuming I need the measure to be filtered by well number.