Finding date when cumulative total crosses dynamic target threshold

[jori]

I'm trying to get a measure to return the date for each month when the cumulative revenue of that month exceeded 50%. So for example, if we made 100k in January, I would like to find the day when we crossed 50k that month.

I've already created the cumulative total per month like this:

_RevenueCumulated = TOTALMTD ( [_RevenueTotal], DateTable[FullDateAlternateKey] )

And for the day I have the following measure:

_RevenueDate50% = 
VAR RevMonth = CALCULATE( [_RevenueTotal], ALLEXCEPT( DateTable, DateTable[Year], DateTable[Month Name] ) )
VAR RevCum = [_RevenueCumulated]
RETURN
    MINX(
        FILTER(
            VALUES(DateTable[FullDateAlternateKey]),
            RevCum >= 0.5 * RevMonth
        ),
        DateTable[FullDateAlternateKey]
    )

This kind of gives me the right result if I put it in a matrix:

The first time we cross the 50% revenue is on the 24th of November, the problem is once I remove the day in the matrix it always goes to the first of the month (as seen in the total).

It works if I try it with a fixed amount like this:

_RevenueDate50% = 
VAR RevCum = [_RevenueCumulated]
RETURN
    MINX(
        FILTER(
            VALUES(DateTable[FullDateAlternateKey]),
            RevCum >= 50000
        ),
        DateTable[FullDateAlternateKey]
    )

So it seems the problem is with the dynamically changing revenue for each month.

Any help is much appreciated!

Thank you

[jori]

Thank you @Anonymous this actually helped.

My working solution is:

_RevenueDate50% = 
VAR RevMonth = CALCULATE( [_RevenueTotal], ALLEXCEPT( DateTable, DateTable[Year], DateTable[Month Name] ) )
VAR CumulativeTable =
    ADDCOLUMNS(
        VALUES(DateTable[FullDateAlternateKey]),
        "CumulativeRevenue", [_RevenueCumulated],
        "RevMonth", RevMonth
    )
RETURN
    MINX(
        FILTER(
            CumulativeTable,
            [CumulativeRevenue] >= 0.5 * [RevMonth]
        ),
        DateTable[FullDateAlternateKey]
    )

[Anonymous · Accepted solution]

Hi @jori ,

This seems to be related to context, which is the possibility to perform dynamic analyses. Understanding the context is important for generating and troubleshooting formulas. More details can be found at the link: Context in DAX Formulas - Microsoft Support.

In this problem, you may be able to create a virtual intermediate table to perform calculations with the help of ADDCOLUMNS or SUMMARIZE functions. Something like

VAR CumulativeTable =

ADDCOLUMNS(

VALUES(DateTable[FullDateAlternateKey]),

"CumulativeRevenue", [_RevenueCumulated]

)

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help we can better understand the problem and help you.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.