cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MrCee Visitor
Visitor

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

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

 

 

PIVOT.png
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.

EXCEL RESULTS.png

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

3 REPLIES 3
marcorusso Member
Member

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

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
)

 

Super User
Super User

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

Hi,

 

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

Super User
Super User

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

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 215 members 2,497 guests
Please welcome our newest community members: