Dax for populating Date by condition

[Anonymous]

Hi All,

i'm trying to write a DAX to calculate the Provision date of a packages that our customre have gotten.

Below is the Current Provision date :

Provision Date = CALCULATE( minx(overall,Overall[DateIn]) ,ALLEXCEPT ( Overall, Overall[CHASSIS],Overall[Package Name]),Overall[Category]="Provision")

CHASSIS	Category	Provision	Utilisation	Closing Balance	DateIn	Package Name	Provision Date
W17144	Provision	1140		1140	9/10/2015	Tweet	9/10/2015
W17144	Utilisation		-$380.	-380	9/10/2015	Tweet	9/10/2015
W17144	Utilisation		-$380.	-380	25/6/2016	Tweet	9/10/2015
W17144	Utilisation		-$380.	-380	1/4/2017	Tweet	9/10/2015
W17144	Provision	1026		1026	16/3/2018	Tweet	9/10/2015
W17144	Utilisation		-$380.	-380	20/3/2018	Tweet	9/10/2015
W17144	Utilisation		-$380.	-380	12/3/2019	Tweet	9/10/2015
W17144	Utilisation		-$380.	-380	4/3/2020	Tweet	9/10/2015
W17144	Provision	1026		1026	5/3/2021	Tweet	9/10/2015
W17144	Utilisation		-$380.	-380	15/3/2021	Tweet	9/10/2015

But i'm trying to dereive the below date instead:

THe provision date is based on the Category = Provision to derieve the date, any utilisation date that fall after the provision date will be using the Provision date until the next provision category was purchase then the provision date should be using re-populate. Any pro or guru able to provide their input?

Expected Result

CHASSIS	Category	Provision	Utilisation	Closing Balance	DateIn	Package Name	Provision Date
W17144	Provision	1140		1140	9/10/2015	Tweet	9/10/2015
W17144	Utilisation		-$380.	-380	9/10/2015	Tweet	9/10/2015
W17144	Utilisation		-$380.	-380	25/6/2016	Tweet	9/10/2015
W17144	Utilisation		-$380.	-380	1/4/2017	Tweet	9/10/2015
W17144	Provision	1026		1026	16/3/2018	Tweet	16/3/2018
W17144	Utilisation		-$380.	-380	20/3/2018	Tweet	16/3/2018
W17144	Utilisation		-$380.	-380	12/3/2019	Tweet	16/3/2018
W17144	Utilisation		-$380.	-380	4/3/2020	Tweet	16/3/2018
W17144	Provision	1026		1026	5/3/2021	Tweet	5/3/2021
W17144	Utilisation		-$380.	-380	15/3/2021	Tweet	5/3/2021

[VahidDM]

Hi @Anonymous 

Try this measure:

Provision Date = 
CALCULATE (
    MAX ( Overall[DateIn] ),
    FILTER (
        ALL ( Overall ),
        Overall[Provision] <> BLANK ()
            && Overall[DateIn] <= MAX ( Overall[DateIn] )
    )
)

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[CNENFRNL · Accepted solution]

Provision Date = 
MAXX(
    FILTER(
        Overall,
        Overall[CHASSIS] = EARLIER( Overall[CHASSIS] )
            && Overall[DateIn] <= EARLIER( Overall[DateIn] )
            && Overall[Category] = "Provision"
    ),
    Overall[DateIn]
)

[amitchandak]

@Anonymous , Try like. New measure

Provision Date = CALCULATE( minx(overall,Overall[DateIn]) ,filter (allselected (Overall), Overall[CHASSIS] = max(Overall[CHASSIS]) && Overall[Package Name] = max(Overall[Package Name]) && Overall[Category]="Provision"))

or

Provision Date = CALCULATE( minx(overall,Overall[DateIn]) ,filter (all(Overall), Overall[CHASSIS] = max(Overall[CHASSIS]) && Overall[Package Name] = max(Overall[Package Name]) && Overall[Category]="Provision"))