Calculate number of days between milestones

[shakirkhanani]

Hi, 

I was wondering whether to calculate number of days between two milestones, for example:

How many days a store took to achieve their first million in sales, and the second million......

I used the following :

First Million = CALCULATE(DISTINCTCOUNT(Sales[Date]),Sales[Amount]<=1000000)

Result seems to be incorrect

Second Million = CALCULATE(DISTINCTCOUNT(Sales[Date]),FILTER(Sales,Sales[Amount]>=1000000 && Sales[Amount]<=2000000)

Result only blank rows

Any suggestions?

[v-yuta-msft]

Hi shakirkhanani ,

"Result seems to be incorrect"

<--- What's the format of date column and your expected value? And could you share a sample file with some dummy data for further analysis?

Regards,

Jimmy Tao

[shakirkhanani]

Thanking for taking interest.   Please find below the link to the sample file, I have removed the unnecessary columns.

https://lattlivcompk-my.sharepoint.com/:x:/g/personal/shakir_khanani_lattliv_com_pk/EeF9wlFqE2xNoce6...

[parry2k]

Are you getting correct result for first million?

[shakirkhanani]

No Sir, its not giving correct answer.

[parry2k]

can you share sample dataset for the solution.

[Zubair_Muhammad]

@shakirkhanani

Please try this MEASURE

I assume you have data like this

Store Code	Date	Amount
PK01	Saturday, January 6, 2018	259,322
PK01	Thursday, January 11, 2018	199,957
PK01	Thursday, January 25, 2018	143,811
PK01	Saturday, February 3, 2018	121,347
PK01	Tuesday, February 6, 2018	220,141
PK01	Wednesday, February 14, 2018	180,620
PK01	Friday, February 16, 2018	267,602
PK01	Friday, March 23, 2018	174,671
PK01	Saturday, April 7, 2018	148,055
PK01	Saturday, April 14, 2018	125,927
PK01	Friday, April 20, 2018	273,066
PK01	Monday, May 7, 2018	154,767
PK01	Sunday, June 3, 2018	181,640
PK01	Saturday, June 23, 2018	283,012

First Million =
VAR temp =
    SUMMARIZE (
        Sales,
        Sales[Date],
        "Cumulative",
        VAR mydate = Sales[Date]
        RETURN
            CALCULATE ( SUM ( Sales[Amount] ), Sales[Date] <= mydate )
    )
VAR DateofAchievemnt =
    MINX ( FILTER ( temp, [Cumulative] > 1000000 ), [Date] )
RETURN
    DATEDIFF ( FIRSTDATE ( Sales[Date] ), DateofAchievemnt, DAY )

 

[shakirkhanani]

Thanks for the help!, let me try and report back to you soon.

[Zubair_Muhammad]

@shakirkhanani

And heres the secondmillion formula

Second Million =
VAR temp =
    SUMMARIZE (
        Sales,
        Sales[Date],
        "Cumulative",
        VAR mydate = Sales[Date]
        RETURN
            CALCULATE ( SUM ( Sales[Amount] ), Sales[Date] <= mydate )
    )
VAR DateofAchievemnt =
    MINX ( FILTER ( temp, [Cumulative] > 2000000 ), [Date] )
RETURN
    DATEDIFF ( FIRSTDATE ( Sales[Date] ), DateofAchievemnt, DAY ) - [First Million]

[Zubair_Muhammad · Accepted solution]

@shakirkhanani

Sample file attached as well

[shakirkhanani]

Awesome! 

Your idea is far better than my work around, which was to calculate running total first and then using Calculate and Filter functions to get required millions.  Although got the same results, but taking running total on larget dataset, really took a toll on my report and slowed it. 

Thanks a million yaar!