topic Re: I want to get Min and Max date and where the category changes in between the dates in DAX Commands and Tips

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

Ankita26 — Mon, 24 Mar 2025 16:35:46 GMT

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%

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

Greg_Deckler — Mon, 24 Mar 2025 17:28:03 GMT

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

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

xifeng_L — Tue, 25 Mar 2025 08:08:43 GMT

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~

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

Ankita26 — Tue, 25 Mar 2025 10:54:44 GMT

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

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

Ankita26 — Tue, 25 Mar 2025 16:54:04 GMT

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

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

techies — Tue, 25 Mar 2025 21:12:26 GMT

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

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

xifeng_L — Wed, 26 Mar 2025 02:39:55 GMT

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.

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

xifeng_L — Wed, 26 Mar 2025 02:41:08 GMT

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

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

Ankita26 — Wed, 26 Mar 2025 05:34:34 GMT

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.

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

Ankita26 — Wed, 26 Mar 2025 05:36:28 GMT

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

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

xifeng_L — Wed, 26 Mar 2025 08:13:06 GMT

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.

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

Ankita26 — Wed, 26 Mar 2025 13:58:48 GMT

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