Measure to show # of days for two metrics to be equal

[sobrien333]

Hello, I have some data that tracks sales over time since a product launch date. I have created a 'New Quick Measure' for Running Total to track total sales from launch date. I am trying to create another measure that will show me how many days it took for 50% of my sales to come in; what we call 'doubling day', and the measure I created for that is:

.Doubling Day Line =

CALCULATE(SUM('Data'[Revenue])/2, ALL('Data'[Days Out]))

For example, if I were to sell 1 unit each day for a year I would have sold 365 units at the end of the year and it would have then taken me 183 days (182.5) to reach 50% of my sales (my doubling day). Each sale transaction in my data table also has 'days out' as a column to show days from product launch. My table looks something like this:

Sale Date	Launch Date	Units Sold	Days Out
1/2/19	1/1/2019	1	1
1/3/19	1/1/2019	2	2
1/4/19	1/1/2019	1	3
1/5/19	1/1/2019	3	4
1/6/19	1/1/2019	1	5
1/7/19	1/1/2019	1	6
1/8/19	1/1/2019	2	7
1/9/19	1/1/2019	2	8
1/10/19	1/1/2019	1	9
1/11/19	1/1/2019	3	10

Any idea how to create that measure?

[Anonymous]

Attached the pbix file below, but..

• Created a data table and related that to your main table. Set that as a date table
• Wrote the following measures (assuming it was a total of yearly sales you are comparing)

Total Units Sold = SUM( Table1[Units Sold] )

Total Yearly Sales = 
IF(
    COUNTROWS( Table1) > 0,
 CALCULATE( [Total Units Sold], FILTER( ALL( DimCalendar), MAX( DimCalendar[Year])= DimCalendar[Year])))

50% of Yearly Sales = [Total Yearly Sales] *.50

Yearly Sales Running Total = 
 TOTALYTD( [Total Units Sold], DimCalendar[Date])

Date when 50% Crossed = 
CALCULATE( FIRSTDATE(DimCalendar[Date]), FILTER( ALL( DimCalendar),[Yearly Sales Running Total]>[50% of Yearly Sales]))

How Many Days = CALCULATE( DISTINCTCOUNT(Table1[Sale Date]), FILTER( ALL( DimCalendar), MAX(DimCalendar[Date]) >= DimCalendar[Date]))

Date When Crossed = if([Date when 50% Crossed] = max( DimCalendar[Date]),[How Many Days])

Final table, though you wouldnt  need or probably want all of them on the table, but I learned to always write out individual measures so they could be used later on if needbe and it's easier to change and troubleshoot:

File:

https://1drv.ms/u/s!Amqd8ArUSwDS3BuleqFOpYwteQLw?e=Zea5ir

[sobrien333]

So I implemented each of the measures you created but for some reason my measure for Total Sales is calculating the same as a rolling (or year to date) total, but yours isn't. My formula is the same though. Any ideas?

[Anonymous]

be sure to set your date table as a date table

[sobrien333 · Accepted solution]

Thanks Nick. I think I identified my problem. I dont' necessarily track sales by year, just overall (could span several years). So I didn't do the Total Yearly Sales measure, I just used a total sales measure. But I think that is what is causing the issue. How can I modify the Total Yearly Sales measure to not be limited to a single year?

[sobrien333]

This looks great, thanks! I'll work in this today.