Calculate the percentage excluding last 5 days

[baijumohan1990]

Hi All,

I am trying to compute the percentage of Free to Paid Sales conversions for a particular period. 

DATE	Free Sales	Paid Sales	Conv %
5/1/2021 0:00	320	225	70%
5/2/2021 0:00	289	194	67%
5/3/2021 0:00	267	153	57%
5/4/2021 0:00	237	142	60%
5/5/2021 0:00	297	194	65%
5/6/2021 0:00	306	134	44%
5/7/2021 0:00	330	133	40%
5/8/2021 0:00	310	120	39%
5/9/2021 0:00	279	93	33%
5/10/2021 0:00	320	112	35%
5/11/2021 0:00	349	111	32%
5/12/2021 0:00	323	127	39%
5/13/2021 0:00	262	82	31%
5/14/2021 0:00	389	101	26%
5/15/2021 0:00	362	100	28%
5/16/2021 0:00	285	74	26%
5/17/2021 0:00	252	76	30%
5/18/2021 0:00	260	72	28%
5/19/2021 0:00	291	131	45%
5/20/2021 0:00	273	64	23%

Sales become paid after the threshold of  5 days. So Conversion % should exclude the last 5 days. Expected output is below.

DATE	Free Sales	Paid Sales	Conv %
5/1/2021 0:00	320	225	70%
5/2/2021 0:00	289	194	67%
5/3/2021 0:00	267	153	57%
5/4/2021 0:00	237	142	60%
5/5/2021 0:00	297	194	65%
5/6/2021 0:00	306	134	44%
5/7/2021 0:00	330	133	40%
5/8/2021 0:00	310	120	39%
5/9/2021 0:00	279	93	33%
5/10/2021 0:00	320	112	35%
5/11/2021 0:00	349	111	32%
5/12/2021 0:00	323	127	39%
5/13/2021 0:00	262	82	31%
5/14/2021 0:00	389	101	26%
5/15/2021 0:00	362	100	28%
5/16/2021 0:00	285	74	0%
5/17/2021 0:00	252	76	0%
5/18/2021 0:00	260	72	0%
5/19/2021 0:00	291	131	0%
5/20/2021 0:00	273	64	0%

Below are the measure i have created.

1. FreeSales      : SUM( DATA[Free_Trials])

2. Conversions : SUM(DATA[Conversions])

3. Conv  %       : Calculate ( DIVIDE( FreeSales/Conversions,0), DATESBETWEEN(DATA[DATE], STARTDATE, ENDDATE-5))

(P.S:  STARTDATE & ENDDATE are the min & max values from the date slicer)

Conv % is not working properly . It giving same value for all the rows in the table.  Please help to fix this issue. 

 Thanks in advance!

[Ashish_Mathur]

Hi,

In an MS Excel file, can you show your formula based working to calculate SalesCon %?  Share the download link of that MS Excel file.  Also, in a card visual, you can only show one figure.  So that figure (SalesCon %) do you want to show on the card visual?  Show that clearly in the MS Excel file.

[baijumohan1990]

i will prepare and update. thanks

[ryan_mayu]

@baijumohan1990 

maybe you can try this

Measure = 
VAR _maxdate=CALCULATE(max('Table'[DATE]),all('Table'))
return if(max('Table'[DATE])>_maxdate-5,0,[paidsales]/[freesales])

[baijumohan1990]

Thanks. but the total is giving 0. also when i use the measure in the card visual getting 0 too. 

[ryan_mayu]

@baijumohan1990 

do you want to display the result in a card visual or a table? What's the expected output for a card visual?

[baijumohan1990]

I need this to be shown in both table and card visual. In card visual it will show the averge conversion % value. 

[ryan_mayu]

@baijumohan1990 

maybe you can create another measure

measure2=averagex(table, measure)