I want to get Min and Max date and where the category changes in between the dates

[Ankita26]

Tank Capacity from 70% to 85% falls under Exception Yellow category, Tank capacity >85% falls under category Exception red

We want to calculate start date and end date based on their number of occurrence and whenever the exception changes and calculate Max tank capacity in between those dates

Raw Data

Material	Location	Exception Category	Date	Tank_Capacity
C150	Chane Botlek	EXCEPTION RED	3/24/2025 0:00	99.17%
C150	Chane Botlek	EXCEPTION RED	3/25/2025 0:00	98.89%
C150	Chane Botlek	EXCEPTION RED	3/26/2025 0:00	98.61%
C150	Chane Botlek	EXCEPTION RED	3/27/2025 0:00	98.34%
C150	Chane Botlek	EXCEPTION RED	3/28/2025 0:00	98.06%
C150	Chane Botlek	EXCEPTION RED	3/29/2025 0:00	97.78%
C150	Chane Botlek	EXCEPTION RED	3/30/2025 0:00	97.50%
C150	Chane Botlek	EXCEPTION RED	3/31/2025 0:00	97.22%
C150	Chane Botlek	EXCEPTION RED	4/1/2025 0:00	96.83%
C150	Chane Botlek	EXCEPTION RED	4/2/2025 0:00	93.48%
C150	Chane Botlek	EXCEPTION RED	4/3/2025 0:00	93.10%
C150	Chane Botlek	EXCEPTION RED	4/4/2025 0:00	92.71%
C150	Chane Botlek	EXCEPTION RED	4/5/2025 0:00	92.33%
C150	Chane Botlek	EXCEPTION RED	4/6/2025 0:00	91.94%
C150	Chane Botlek	EXCEPTION YELLOW	4/7/2025 0:00	82.29%
C150	Chane Botlek	EXCEPTION YELLOW	4/8/2025 0:00	81.91%
C150	Chane Botlek	EXCEPTION YELLOW	4/9/2025 0:00	77.81%
C150	Chane Botlek	EXCEPTION YELLOW	4/10/2025 0:00	77.43%
C150	Chane Botlek	EXCEPTION YELLOW	4/11/2025 0:00	77.04%
C150	Chane Botlek	EXCEPTION YELLOW	4/12/2025 0:00	76.66%
C150	Chane Botlek	EXCEPTION YELLOW	4/13/2025 0:00	74.42%
C150	Chane Botlek	EXCEPTION YELLOW	4/14/2025 0:00	74.04%
C150	Chane Botlek	EXCEPTION YELLOW	4/15/2025 0:00	73.65%
C150	Chane Botlek	EXCEPTION YELLOW	4/16/2025 0:00	73.27%
C150	Chane Botlek	EXCEPTION YELLOW	4/17/2025 0:00	72.88%
C150	Chane Botlek	EXCEPTION YELLOW	4/18/2025 0:00	72.50%
C150	Chane Botlek	EXCEPTION YELLOW	4/19/2025 0:00	72.11%
C150	Chane Botlek	EXCEPTION YELLOW	4/20/2025 0:00	71.72%
C150	Chane Botlek	EXCEPTION YELLOW	4/21/2025 0:00	71.34%
C150	Chane Botlek	EXCEPTION YELLOW	4/22/2025 0:00	70.95%
C150	Chane Botlek	EXCEPTION YELLOW	4/23/2025 0:00	70.57%
C150	Chane Botlek	EXCEPTION YELLOW	4/24/2025 0:00	70.18%
C150	Chane Botlek	EXCEPTION RED	5/5/2025 0:00	100.85%
C150	Chane Botlek	EXCEPTION RED	5/6/2025 0:00	100.47%
C150	Chane Botlek	EXCEPTION RED	5/7/2025 0:00	100.10%
C150	Chane Botlek	EXCEPTION RED	5/8/2025 0:00	99.73%
C150	Chane Botlek	EXCEPTION RED	5/9/2025 0:00	99.35%
C150	Chane Botlek	EXCEPTION RED	5/10/2025 0:00	98.98%
C150	Chane Botlek	EXCEPTION RED	5/11/2025 0:00	98.61%
C150	Chane Botlek	EXCEPTION RED	5/12/2025 0:00	98.24%
C150	Chane Botlek	EXCEPTION RED	5/13/2025 0:00	97.86%
C150	Chane Botlek	EXCEPTION RED	5/14/2025 0:00	97.49%
C150	Chane Botlek	EXCEPTION RED	5/15/2025 0:00	97.12%
C150	Chane Botlek	EXCEPTION RED	5/16/2025 0:00	96.75%
C150	Chane Botlek	EXCEPTION RED	5/17/2025 0:00	96.37%
C150	Chane Botlek	EXCEPTION RED	5/18/2025 0:00	96.00%
C150	Chane Botlek	EXCEPTION YELLOW	5/19/2025 0:00	80.80%
C150	Chane Botlek	EXCEPTION YELLOW	5/20/2025 0:00	80.43%
C150	Chane Botlek	EXCEPTION YELLOW	5/21/2025 0:00	80.06%
C150	Chane Botlek	EXCEPTION YELLOW	5/22/2025 0:00	79.68%
C150	Chane Botlek	EXCEPTION YELLOW	5/23/2025 0:00	79.31%
C150	Chane Botlek	EXCEPTION YELLOW	5/24/2025 0:00	78.94%
C150	Chane Botlek	EXCEPTION YELLOW	5/25/2025 0:00	78.57%
C150	Chane Botlek	EXCEPTION YELLOW	5/26/2025 0:00	78.19%
C150	Chane Botlek	EXCEPTION YELLOW	5/27/2025 0:00	77.82%
C150	Chane Botlek	EXCEPTION YELLOW	5/28/2025 0:00	77.45%
C150	Chane Botlek	EXCEPTION YELLOW	5/29/2025 0:00	77.08%
C150	Chane Botlek	EXCEPTION YELLOW	5/30/2025 0:00	76.70%
C150	Chane Botlek	EXCEPTION YELLOW	5/31/2025 0:00	76.33%
C150	Chane Botlek	EXCEPTION YELLOW	6/1/2025 0:00	75.94%
C150	Chane Botlek	EXCEPTION YELLOW	6/2/2025 0:00	75.56%
C150	Chane Botlek	EXCEPTION YELLOW	6/3/2025 0:00	75.17%
C150	Chane Botlek	EXCEPTION YELLOW	6/4/2025 0:00	74.79%
C150	Chane Botlek	EXCEPTION YELLOW	6/5/2025 0:00	74.40%
C150	Chane Botlek	EXCEPTION YELLOW	6/6/2025 0:00	74.02%
C150	Chane Botlek	EXCEPTION YELLOW	6/7/2025 0:00	73.63%
C150	Chane Botlek	EXCEPTION RED	6/8/2025 0:00	136.62%
C150	Chane Botlek	EXCEPTION RED	6/9/2025 0:00	136.24%
C150	Chane Botlek	EXCEPTION RED	6/10/2025 0:00	135.85%
C150	Chane Botlek	EXCEPTION RED	6/11/2025 0:00	135.47%
C150	Chane Botlek	EXCEPTION RED	6/12/2025 0:00	135.08%
C150	Chane Botlek	EXCEPTION RED	6/13/2025 0:00	134.70%
C150	Chane Botlek	EXCEPTION RED	6/14/2025 0:00	134.31%
C150	Chane Botlek	EXCEPTION RED	6/15/2025 0:00	133.92%
C150	Chane Botlek	EXCEPTION RED	6/16/2025 0:00	133.54%
C150	Chane Botlek	EXCEPTION RED	6/17/2025 0:00	133.15%
C150	Chane Botlek	EXCEPTION RED	6/18/2025 0:00	132.77%

Expected result is

Material	Location	Exception Category	Start Date	End Date	Max Tank_Capacity
C150	Chane Botlek	EXCEPTION RED	3/24/2025 0:00	4/6/2025 0:00	99.17%
C150	Chane Botlek	EXCEPTION YELLOW	4/7/2025 0:00	4/24/2025 0:00	82.29%
C150	Chane Botlek	EXCEPTION RED	5/5/2025 0:00	5/18/2025 0:00	100.85%
C150	Chane Botlek	EXCEPTION YELLOW	5/19/2025 0:00	6/7/2025 0:00	80.80%
C150	Chane Botlek	EXCEPTION RED	6/8/2025 0:00	6/18/2025 0:00	136.62%

[techies]

Hi @Ankita26 

Before creating measures for Start Date, End Date, and Tank Capacity, I guess you need to first create calculated columns like this to structure the data properly.

Category Change =

VAR PrevCategory =

    CALCULATE(

        MAX('Sheet1'[Exception Category]),

        FILTER(

            'Sheet1',

            'Sheet1'[Material] = EARLIER('Sheet1'[Material]) &&

            'Sheet1'[Location] = EARLIER('Sheet1'[Location]) &&

            'Sheet1'[Date] < EARLIER('Sheet1'[Date])

        )

    )

RETURN

IF(

    PrevCategory <> 'Sheet1'[Exception Category], 1, 0

)

Group ID =

VAR RunningTotal =

    SUMX(

        FILTER(

            'Sheet1',

            'Sheet1'[Material] = EARLIER('Sheet1'[Material]) &&

            'Sheet1'[Location] = EARLIER('Sheet1'[Location]) &&

            'Sheet1'[Date] <= EARLIER('Sheet1'[Date])

        ),

        'Sheet1'[Category Change]

    )

RETURN

RunningTotal

[Ankita26]

Hi @techies Thanks for the solution. But in my dataset I am not able to create a new column in my file. And the earlier function doesnot work when we use a measure.

[xifeng_L · Accepted solution]

Hi @Ankita26 

You can add two calculated columns as below :

Start Date = 
VAR Tbl = CALCULATETABLE('Table',ALLEXCEPT('Table','Table'[Material],'Table'[Location]))
VAR PreStartDate = 
    MAXX(
        FILTER(
            Tbl,
            AND(
                'Table'[Date]<EARLIER('Table'[Date]),
                'Table'[Exception Category]<>EARLIER('Table'[Exception Category])
            )
        ),
        'Table'[Date]
    )
RETURN
COALESCE(
    MINX(FILTER(Tbl,'Table'[Date]>PreStartDate ),'Table'[Date]),
    MINX(Tbl,'Table'[Date])
)

End Date = 
VAR Tbl = CALCULATETABLE('Table',ALLEXCEPT('Table','Table'[Material],'Table'[Location]))
VAR NextEndDate = 
    MINX(
        FILTER(
            Tbl,
            AND(
                'Table'[Date]>EARLIER('Table'[Date]),
                'Table'[Exception Category]<>EARLIER('Table'[Exception Category])
            )
        ),
        'Table'[Date]
    )
RETURN
COALESCE(
    MAXX(FILTER(Tbl,'Table'[Date]<NextEndDate),'Table'[Date]),
    MAXX(Tbl,'Table'[Date])
)

Then, create a table to get the desired results:

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[Ankita26]

I am not able to create calculated column. The only option I have is to create calculate measure

[xifeng_L]

Since the same items are automatically merged in the matrix, you have to add the calculated columns, and just using the metrics won't give you the effect you're looking for.

[Ankita26]

So is there any workaround to have the logic that you provided can be done in calculated measure. As I don't have option to create calculated column in my dataset. And we can't use earlier function function in measure

[xifeng_L]

This is not just a matter of switching to measures, because of the auto aggregation nature of the matrix, you have to add columns to the table to achieve what you need.

Based on your screenshot, your model should be directly using the dataset on PowerBI Server, you can switch to DirectQuery by clicking on the option in the bottom right corner, which will can create the calculated columns.

If that method doesn't work either, then you may need to contact the Owner of that dataset to get help adding those two calculated columns.

[Ankita26]

Thanks alot! I am able to ge the expected result.

[Ankita26]

Thanks for providing the solution but I am not able to use earlier function here. 

[xifeng_L]

Not sure about the scenario of your error, you can refer to my pbix file

[Greg_Deckler]

@Ankita26 Take a look at Cthulhu and Streaks because I'm pretty sure the solution starts there. 

https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Streaks/td-p/4051984

https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211