DAX Measure - A Cumulative Running Total which returns a DATE when Target is achieved

[MrCee]

Hi community,

Without using calculated columns as I would like to be able to pivot by project in Power BI using measures,
I need to find a way to extract the exact date when the cumulative running total measure has exceeded a target.

This target can be found repeating project by project in the same table, or alternatively as a RELATED lookup. I'm using Power BI but visualising measures in Power Pivot / Excel for testing purposes

The cumulative running total is calculated via a standard DAX pattern as such and as expected equals total sales as the measure includes all rows and is not yet filtered by TARGET.

CUMULATIVE RUNNING TOTAL:=CALCULATE(
SUM(SALES[AMOUNT]),
FILTER(SALES,
SALES[Sales_Date]>=MIN(SALES[Sales_Date])))

What I would like to achieve next is to find the cumulative running total measure by project exact ROW showing MINIMUM 'TARGET HIT DATE' where the sales TARGET has been exceeded by the cumulative total measure.

I have filtered the following table on PROJECT #3 with some manual Excel columns to show you the ROW which should be returned, and the VALUE.



At present, this does not work. I'm hoping it helps show what I am trying to achieve:

HIT TARGET DATE:=CALCULATE(
MIN(SALES[Sales_Date]),
FILTER(SALES,[CUMULATIVE RUNNING TOTAL]>=RELATED(PROJECTS[TARGET])))

or if the target value repeats in the same table for each project as shown in the above screen capture

HIT TARGET DATE:=CALCULATE(
MIN(SALES[Sales_Date]),
FILTER(SALES,[CUMULATIVE RUNNING TOTAL]>=MIN(SALES[TARGET])))

I probably missing something really simple here, but I have not seen a solution so far for this one.
Thanks in advance!

[Ashish_Mathur]

Hi,

Share the link from where i can download your formula driven Excel file.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Sumdib]

Can anyone provide the measure in the solution?

[Ashish_Mathur]

Hi,

Are you not able to download my PBI dolution file?

[marcorusso]

I don't think it is a good idea to get rid of a Date table.

Creating a calculated column with the TARGET_ACHIEVED column could be much better for the performance.
However, this code should work as a measure (didn't try it, bugs are possible!)

DateTargetReached :=
IF (
    HASONEVALUE ( Projects[Project_Name] ),
    VAR TargetRunningTotal =
        SELECTEDVALUE ( Projects[Target] )
    VAR FirstDateOverTarget =
        FIRSTNONBLANK (
            Sales[Date],
            IF (
                [Cumulative Running Total] >= TargetRunningTotal,
                1
            )
        )
    RETURN
        FirstDateOverTarget
)

[BastiaanBrak]

thanks Marco, I've been trying to modify your solution for my own use case but not quite getting there. Hoping you might be able to help me with this?

To clarify: suppose that the OP was not interested in the date when the target was hit per project but in the earliest (and latest) date overall (across far more projects). When removing the IF plus HASONEVALUE condition, the Cumulative Running Total aggregates across projects and therefore the result would give a 'target hit date' that is earlier than reality so that doesn't work. What is the required modification to your DAX above that would make it work in the scenario I described above?

[Ashish_Mathur]

Hi,

Share some data, describe the question and show the expected result.

[BastiaanBrak]

hi @Ashish_Mathur and thanks for taking an interest in my question. My use case is a little different to the OP but in essence very similar. Suppose I have the following simple data table called Test, with two (or more) distinct IDs:

Using DAX I have created a 'Running total' measure:

Running total =

CALCULATE(

    SUM('Test'[Value])/DISTINCTCOUNT('Test'[ID]),

    FILTER(

        ALLSELECTED('Test'[Date]),

        ISONORAFTER('Test'[Date], MAX('Test'[Date]), DESC)

    )

)

What I want to do is have two Cards on the dashboard indicating the earliest and latest date on which a particular target, say, 325 is exceeded. Graphically: 

I've tried adapting the measure that @marcorusso created:

FirstDateTargetReached =

IF (

    HASONEVALUE ( Test[ID] ),

    VAR TargetRunningTotal = 325

    VAR FirstDateOverTarget =

        FIRSTNONBLANK (

            Test[Date],

            IF (

                [Running total] >= TargetRunningTotal,

                1

            )

        )

    RETURN

        FirstDateOverTarget

)

but this is not suitable here because it requires a single ID to be selected; unless an ID is filtered (e.g. by clicking an ID entry in the Legend) the Card will show (BLANK).

So the question is: which DAX measures enable the Card visuals to show the earliest and latest dates a given target is exceeded?

.pbix with the above is available here: https://easyupload.io/i9ubac

Many thanks, Bastiaan

[marcorusso]

Do you want this?

FirstDateTargetReached =
MINX (
    DISTINCT ( Test[ID] ),
    CALCULATE (
        VAR TargetRunningTotal = 325
        VAR FirstDateOverTarget =
            FIRSTNONBLANK ( Test[Date], IF ( [Running total] >= TargetRunningTotal, 1 ) )
        RETURN
            FirstDateOverTarget
    )
)

[lance_6]

@marcorusso 

What is the benefit of using DISTINCT vs VALUES here? 

[marcorusso]

In case Test is on the one-side of a relationship, you don't want to iterate the BLANK value of an invalid relationship.

[lance_6]

That makes sense. Thank you for the response!

[BastiaanBrak]

@marcorusso just wanted to say thanks again! I modified your DAX solution for my specific use case. ICYMBI, the report is published here: Willow-carrot aphid migration forecast tool | AHDB

[BastiaanBrak]

Yes @marcorusso, thank you so much! 

[Ashish_Mathur]

Hi,

I do not understand the meaning of "earliest and latest dates a given target is exceeded".  Based on that data that you have shared, please share your exact result expected.

[BastiaanBrak]

@Ashish_Mathur the exact result I expect based on my test data is that the Card visual with the 'FirstDateTargetReached' will display '27 March', given that was the earliest date on which the target was exceeded (see Tooltip): 

Likewise, the Card visual with the 'LastDateTargetReached' should display '30 March', given that was the latest date on which the target was exceeded (again see Tooltip): 

My actual dataset has more IDs than two but I'm assuming that the principle is the same.

Thanks Bastiaan