GIve the date when a certain type of product sales first time exceeds a value

[Qianru221]

Hi all,

Here is the problem description.

I have a column of product names A,B,C,D....

I have entries of product sales revenue from each day. (colums of product names, date of sales, sales revenue value)

I would like to have another column to show when e.g. accumulated product A sales reaches 1M. (The date it becomes a so-called mature product in the market.) 

How I should formulate the DAX in a calculated field /  a customized column?

Thanks in advance.

[Zubair_Muhammad]

Hi @Qianru221

Try this

First Add a Measure

Cumulative =
SUMX (
    FILTER (
        ALL ( TableName[ Date ] ),
        TableName[ Date ] <= SELECTEDVALUE ( TableName[ Date ] )
    ),
    CALCULATE ( SUM ( TableName[ Revenue ] ) )
)

Then you can get the first date sales reach 1 Miilion using this MEASURE

Date when 1 Million reached =
TOPN (
    1,
    FILTER ( ALL ( TableName[ Date ] ), [Cumulative] > 1000000 ),
    [Cumulative], ASC
)

[Qianru221]

Hi，

Thank you for your help!

Would it be possible to have it for each product A,B,C,D....? Cos each product have a culmulative revenue, and the date is only marked out when the number the culmulative revenue is reached for each product. But product A,B,C,D may all have a different date.

[Zubair_Muhammad]

@Qianru221

Do you have a Cumulative Revenue Column already?

[Zubair_Muhammad]

@Qianru221

Could you paste your dataset and desired OUTPUT?

[Zubair_Muhammad]

@Qianru221

Try this MEASURE.

Date when 1 Million reached =
VAR RESULT =
    TOPN (
        1,
        FILTER ( ALL ( TableName[ Date ] ), [Cumulative] > 1000000 ),
        [Cumulative], ASC
    )
RETURN
    IF ( SELECTEDVALUE ( TableName[ Date ] ) = RESULT, Result )

You will get

[Qianru221]

Thanks

This is exactly what I want!

However now I am still having difficulty with the culmulative measure. The one you wrote earlier on does not have a filter on product type or?

[Zubair_Muhammad · Accepted solution]

@Qianru221

Please check the file attached in my earlier post

It works with the slicer for Products

[Qianru221]

HI @Zubair_Muhammad

I have worked out the solution you provided me with.

Is there a way to work without the slicers? So that I will be able to have the date for each product. (Because later I need to create a table including product and the date when the sales volumn reaches 1M)

Many thanks!!

Best,

Qianru

[Qianru221]

No. I dont have one.

I only have a query containing lines of entries of, product type (a,b,c,d....) and time sold (dates) and revenue earned for that sale.

[Zubair_Muhammad]

@Qianru221

See the attached sample file here as well