Running minimum within date ranges

[Novice_YVR]

Hi everyone,

I am trying to figure out how to calculate a running minimum value within certain timeframes. 

Let's say I am working with the data below. 

For each "Account", I need to calculate the minimum within the last 5 days as an example (I assume the formula would be adjusted for different ranges like 30 days, 1 year, etc.).

For 8/16 to 8/19, the value should be 0 as 5 days hasn't passed yet and my expected values are in the last column. 

Date	Account	Value	Minimum within last 5 days
8/16/2023	A	100
8/17/2023	A	200
8/18/2023	A	300
8/19/2023	A	400
8/20/2023	A	500	100
8/21/2023	A	600	200
8/22/2023	A	700	300
8/23/2023	A	800	400
8/24/2023	A	900	500
8/25/2023	A	1000	600
8/26/2023	A	1100	700
8/27/2023	A	1200	800
8/28/2023	A	1300	900
8/29/2023	A	1400	1000
8/30/2023	A	1500	1100
8/31/2023	A	1600	1200
9/1/2023	A	1700	1300
9/2/2023	A	1800	1400
9/3/2023	A	1900	1500
9/4/2023	A	2000	1600
9/5/2023	A	2100	1700
9/6/2023	A	2200	1800
9/7/2023	A	2300	1900
9/8/2023	A	2400	2000
9/9/2023	A	2500	2100
9/10/2023	A	2600	2200
9/11/2023	A	2700	2300
9/12/2023	A	2800	2400
9/13/2023	A	2900	2500
9/14/2023	A	3000	2600
9/15/2023	A	3100	2700
9/16/2023	A	3200	2800
9/17/2023	A	3300	2900
9/18/2023	A	3400	3000
9/19/2023	A	3500	3100
9/20/2023	A	3600	3200
8/16/2023	B	100
8/17/2023	B	200
8/18/2023	B	300
8/19/2023	B	400
8/20/2023	B	500	100
8/21/2023	B	600	200
8/22/2023	B	700	300
8/23/2023	B	800	400
8/24/2023	B	900	500
8/25/2023	B	1	1
8/26/2023	B	1100	1
8/27/2023	B	1200	1
8/28/2023	B	1300	1
8/29/2023	B	1400	1
8/30/2023	B	1500	1100
8/31/2023	B	1600	1200
9/1/2023	B	1700	1300
9/2/2023	B	1800	1400
9/3/2023	B	1900	1500
9/4/2023	B	2000	1600
9/5/2023	B	2100	1700
9/6/2023	B	2200	1800
9/7/2023	B	2300	1900
9/8/2023	B	2400	2000
9/9/2023	B	2	2
9/10/2023	B	2600	2
9/11/2023	B	2700	2
9/12/2023	B	2800	2
9/13/2023	B	2900	2
9/14/2023	B	3000	2600
9/15/2023	B	3100	2700
9/16/2023	B	3200	2800
9/17/2023	B	3300	2900
9/18/2023	B	3400	3000
9/19/2023	B	3500	3100
9/20/2023	B	3600	3200

I have searched and tried to adapt from the following measures in these links but always end up with a ciruclar dependency error. 

Solved: Re: Filter minimum value per category within date ... - Microsoft Fabric Community

Solved: lookup value within date range - Microsoft Fabric Community

Solved: Re: Calculate Min Values within a Specific and Dyn... - Microsoft Fabric Community

Thank you and much appreciation. 

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column, not a measure.

Minimum within last 5 days CC =
VAR _t =
    WINDOW (
        -4,
        REL,
        0,
        REL,
        SUMMARIZE ( Data, Data[Date], Data[Account], Data[Value] ),
        ORDERBY ( Data[Date], ASC ),
        ,
        PARTITIONBY ( Data[Account] )
    )
VAR _condition =
    COUNTROWS ( _t ) = 5
RETURN
    IF ( _condition, MINX ( _t, Data[Value] ), 0 )