Need a measure to return minimum dates for each rows

[Anonymous]

I am trying to return the first minimum date based on the group of -ve and -ve value for all the rows .

I need a measure to calculate the minimum date here because stock is calculated based on two different tables.

See the examples as below. 

date	material	stock
6/1/2022	101	-200
7/1/2022	101	-100
8/1/2022	101	100
9/1/2022	101	200
10/1/2022	101	-100
11/1/2022	101	-50

Expected Result :

date	material	stock	result
6/1/2022	101	-200	6/1/2022
7/1/2022	101	-100	6/1/2022
8/1/2022	101	100	8/1/2022
9/1/2022	101	200	8/1/2022
10/1/2022	101	-100	10/1/2022
11/1/2022	101	-50	10/1/2022

Can someone help me on this please?

@amitchandak @Ashish_Mathur 

[Anonymous]

thanks again @Jihwan_Kim  have a good day 🙂

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I assumed the expected outcome shows minimum date for the groups of minus stocks or plus stocks.

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file, and each of them are numbered as a step to follow.

step one plus minus change index: = 
VAR _currentstock =
    SUM ( Data[stock] )
VAR _currentdate =
    MAX ( Data[date] )
VAR _currentmaterial =
    MAX ( Data[material] )
VAR _previousdate =
    MAXX (
        FILTER (
            ALL ( Data ),
            Data[material] = _currentmaterial
                && Data[date] < _currentdate
        ),
        Data[date]
    )
VAR _previousstock =
    SUMX (
        FILTER (
            ALL ( Data ),
            Data[material] = _currentmaterial
                && Data[date] = _previousdate
        ),
        Data[stock]
    )
RETURN
IF( HASONEVALUE( Data[date]),
    IF ( _currentstock * _previousstock < 0, 1, 0 )
)

step two Group of change index: = 
IF (
    HASONEVALUE ( Data[date] ),
    SUMX (
        FILTER (
            ALL ( Data ),
            Data[material] = MAX ( Data[material] )
                && Data[date] <= MAX ( Data[date] )
        ),
        [step one plus minus change index:]
    )
)

step three group by step two: = 
VAR _currentgroup = [step two Group of change index:]
VAR _newtable =
    FILTER (
        ADDCOLUMNS (
            FILTER ( ALL ( Data ), Data[material] = MAX ( Data[material] ) ),
            "@group", [step two Group of change index:],
            "@stock", CALCULATE ( SUM ( Data[stock] ) )
        ),
        [@group] = _currentgroup
    )
VAR _findminstock =
    MAXX (
        GROUPBY ( _newtable, [@group], "@minstock", MINX ( CURRENTGROUP (), [@stock] ) ),
        [@minstock]
    )
RETURN
    IF (
        HASONEVALUE ( Data[date] ),
        MAXX ( FILTER ( _newtable, Data[stock] = _findminstock ), Data[date] )
    )

[Anonymous]

Thank you @Jihwan_Kim  for your support. Yes you are absolutely correct.

I tried to replicate the same in my current report but unable to resolve it.

here is the file :

https://drive.google.com/file/d/1-HdbdhwZ_Voxb1-tjjsd8sTlKzolXLAC/view?usp=drivesdk

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your feedback.

Please check the attached file below.