Previous Test Date

Question

I am trying to recreate the following 3 spotfire calculated columns in Power Bi. I am trying to pull the previous test date for each oil well in my table. Every well has a unique identifier (comp_sk), and each well has historical test dates. I have the table filtered so that it only displays wells that have been tested on the day the data is pulled. I need to pull the previous test date as well. I am having trouble with the Value Max calcualted column. I am open to simplifying the the equations into one if possible. Thanks

Test Date Rank = DenseRank([TEST DATE],"desc",[COMP_SK])

Previous Test Date1 = case when [Test Date Rank]=2 then [TEST DATE] else null end

Previous Test Date = ValueForMax([Production Date],[Previous Test Date1]) over (Intersect([COMP_SK],AllPrevious([Production Date])))

This is what I have come up with so far:

Test Date Rank =

VAR D = PNRDailyAllocatedProduction[Test Date].[Date]

VAR C = PNRDailyAllocatedProduction[COMP_SK]

RETURN

CALCULATE(

RANK.EQ(D,PNRDailyAllocatedProduction[Test Date].[Date],DESC),

FILTER(ALL(PNRDailyAllocatedProduction),PNRDailyAllocatedProduction[COMP_SK]=C))

Previous Test Date1 = IF(PNRDailyAllocatedProduction[Test Date Rank] = 2,PNRDailyAllocatedProduction[Test Date].[Date],BLANK())

Previous Test Date =

VAR C = PNRDailyAllocatedProduction[COMP_SK]

RETURN

MAXX(

FILTER(ALL(PNRDailyAllocatedProduction),PNRDailyAllocatedProduction[COMP_SK]=C),

PNRDailyAllocatedProduction[Previous Test Date1].[Date])

amitchandak · Answer

Anonymous , what are trying to do with help from previous date 
you can use RANKX
RANX(all(PNRDailyAllocatedProduction),PNRDailyAllocatedProduction[Test Date]) 
&nbsp;
if you need last date as column&nbsp;
new column = maxx( filter(PNRDailyAllocatedProduction,[Test Date]&lt;earlier([Test Date])),[Test Date])
&nbsp;
In case of measure refer my blog
Power BI — Week on Week and WTD https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
&nbsp;
For Rank Refer these linkshttps://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measureshttps://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columnshttps://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finalehttps://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

v-jingzhang · Answer

Hi&nbsp;Anonymous&nbsp;&nbsp;I'm not sure if I understand your desired result clearly. If you want both the latest test date and its previous test date, you could try below codes. I used your old [Test Date Rank] column in it.Last Two Test Date = 
IF (
    PNRDailyAllocatedProduction[Test Date Rank] IN { 1, 2 },
    PNRDailyAllocatedProduction[TEST DATE],
    BLANK ()
)&nbsp;Or you could also try another column codes:Last Two Test Date 2 = 
VAR C = PNRDailyAllocatedProduction[COMP_SK]
VAR lastTestDate =
    MAXX (
        FILTER ( PNRDailyAllocatedProduction, PNRDailyAllocatedProduction[COMP_SK] = C ),
        PNRDailyAllocatedProduction[TEST DATE]
    )
VAR _2ndlastTestDate =
    MAXX (
        FILTER (
            PNRDailyAllocatedProduction,
            PNRDailyAllocatedProduction[COMP_SK] = C
                &amp;&amp; PNRDailyAllocatedProduction[TEST DATE] &lt; lastTestDate
        ),
        PNRDailyAllocatedProduction[TEST DATE]
    )
RETURN
    IF (
        PNRDailyAllocatedProduction[TEST DATE] IN { lastTestDate, _2ndlastTestDate },
        PNRDailyAllocatedProduction[TEST DATE],
        BLANK ()
    )Kindly let me know if this helps.Community Support Team _ Jing ZhangIf this post helps, please consider Accept it as the solution to help other members find it.

anonymous · Answer

v-jingzhang I think you were on the right track, but the code needs to be altered a bit. I have filtered the table below to 2 days behind for data accuracy and production date = current well test date. As you can see in the table below each well that was tested on 12/21/2020 has been pulled and its corresponding oil test. Along with the previous test date (no matter how far back it was) and its corresponding oil test on that previous date. The best way to make sure you are pulling the right well information is to use the wells unique identifier (comp_sk) which is hidden in the table below. So in short I just need help with the syntax to pull the previous date per comp_sk that is < than the current test date over all production dates.

anonymous · Answer

I have filtered the table below to 2 days behind for data accuracy and production date = current well test date. As you can see in the table below each well that was tested on 12/21/2020 has been pulled and its corresponding oil test. Along with the previous test date (no matter how far back it was) and its corresponding oil test on that previous date. The best way to make sure you are pulling the right well information is to use the wells unique identifier (comp_sk) which is hidden in the table below. So in short I just need help with the syntax to pull the previous date per comp_sk that is < than the current test date over all production dates.

v-jingzhang · Answer

AnonymousOk, I get it. Please try below codes:Previous Test Date 2 = 
VAR maxTestDate =
    CALCULATE (
        MAX ( PNRDailyAllocatedProduction[TEST DATE] ),
        ALLEXCEPT ( PNRDailyAllocatedProduction, PNRDailyAllocatedProduction[COMP_SK] )
    )
RETURN
    CALCULATE (
        MAX ( PNRDailyAllocatedProduction[TEST DATE] ),
        ALLEXCEPT ( PNRDailyAllocatedProduction, PNRDailyAllocatedProduction[COMP_SK] ),
        PNRDailyAllocatedProduction[TEST DATE] &lt; maxTestDate
    )&nbsp;Best Regards,JingIf this post helps, please consider Accept it as the solution to help other members find it.

Forum Discussion

Previous Test Date

7 Replies

Related content

Unit testing in report

Previous date values

Previous Year calculation based on date slicer -- multiple date / discontinuous date selected

Return previous Sunday Date

Previous Year To Date