Index Measure for Rolling

[Nimai123]

I want my Rolling ACV Measure to be displayed from FY17-Q4, so I created a measure Index for Rolling where I wanted it to behave in a manner which makes the ACV Measure to start from FY17-Q4

Index for rolling = CALCULATE(DISTINCTCOUNT(derived_dimdate_2[fiscal_quarter_year_qtd]),DATESINPERIOD(derived_dimdate_2[fulldate],ENDOFMONTH(derived_dimdate_2[fulldate]),-4,QUARTER),NOT(ISBLANK(bookings_reporting_view[Maintenance End Date])))

Relationship

Previously Worked well for me in a different dataset but require to be implemented same here

@amitchandak 

@Greg_Deckler 

[Greg_Deckler]

@Nimai123 I've been looking this and I still don't know what you want exactly. What shows what you want as the output? Can you post sample data as text and expected output from sample data and why you should get that result? Going to need to mock this up to figure it out.

[Nimai123]

Hello @Greg_Deckler 

I need the output in this manner where my table is starting to display from 17-Q4

fiscal_quarter_year_qtd	Cancelled ACV	Rolling ACV
FY17 - Q4	$3,622,089.83	4393159.24
FY18 - Q1	$6,757,638.37	11100011.31
FY18 - Q2	$8,664,261.89	19628983.49
FY18 - Q3	$12,289,999.57	31333989.66
FY18 - Q4	$11,132,466.41	38844366.24
FY19 - Q1	$9,840,930.33	41927658.2
FY19 - Q2	$9,935,995.76	43199392.07
FY19 - Q3	$14,463,339.54	45372732.04
FY19 - Q4	$11,474,599.48	45714865.11
FY20 - Q1	$7,748,718.87	43622653.65
FY20 - Q2	$10,039,563.14	43726221.03
FY20 - Q3	$18,549,398.19	47812279.68
FY20 - Q4	$15,284,933.31	51622613.51
FY21 - Q1	$9,998,708.78	53872603.42
FY21 - Q2	$9,488,384.42	53321424.7
FY21 - Q3	$605,659.06	35377685.57
FY21 - Q4	$83,040.48	20175792.74
FY22 - Q1	$3,975.33	10181059.29
FY22 - Q2	$37,403.87	730078.74
FY22 - Q3	$112,312.56	236732.24
FY22 - Q4	$24,723.91	178415.67
FY23 - Q1	$0	174440.34
FY23 - Q2	$0	137036.47
FY23 - Q3	$1,101.42	25825.33
FY23 - Q4	$0	1101.42
FY24 - Q1	$0	1101.42
FY24 - Q2		1101.42
FY24 - Q3		0

Sample data

fiscal_quarter_year_qtd	Cancelled ACV	Rolling ACV
FY15 - Q3	$47,748.94	47748.94
FY15 - Q4	$17,095.77	64844.71
FY16 - Q1	$33,935.73	98780.44
FY16 - Q2	$13,805.46	112585.9
FY16 - Q3	$166,304.32	231141.28
FY16 - Q4	$26,638.80	240684.31
FY17 - Q1	$50,786.30	257534.88
FY17 - Q2	$135,289.71	379019.13
FY17 - Q3	$584,993.40	797708.21
FY17 - Q4	$3,622,089.83	4393159.24
FY18 - Q1	$6,757,638.37	11100011.31
FY18 - Q2	$8,664,261.89	19628983.49
FY18 - Q3	$12,289,999.57	31333989.66
FY18 - Q4	$11,132,466.41	38844366.24
FY19 - Q1	$9,840,930.33	41927658.2
FY19 - Q2	$9,935,995.76	43199392.07
FY19 - Q3	$14,463,339.54	45372732.04
FY19 - Q4	$11,474,599.48	45714865.11
FY20 - Q1	$7,748,718.87	43622653.65
FY20 - Q2	$10,039,563.14	43726221.03
FY20 - Q3	$18,549,398.19	47812279.68
FY20 - Q4	$15,284,933.31	51622613.51
FY21 - Q1	$9,998,708.78	53872603.42
FY21 - Q2	$9,488,384.42	53321424.7
FY21 - Q3	$605,659.06	35377685.57
FY21 - Q4	$83,040.48	20175792.74
FY22 - Q1	$3,975.33	10181059.29
FY22 - Q2	$37,403.87	730078.74
FY22 - Q3	$112,312.56	236732.24
FY22 - Q4	$24,723.91	178415.67
FY23 - Q1	$0	174440.34
FY23 - Q2	$0	137036.47
FY23 - Q3	$1,101.42	25825.33
FY23 - Q4	$0	1101.42
FY24 - Q1	$0	1101.42
FY24 - Q2		1101.42
FY24 - Q3		0
FY24 - Q4		0

I have achieved this same scenario before with the help of index measure but I don't know why I am not able to get it this time.

Pbix file

https://drive.google.com/drive/folders/1Wz51npWQ-8gZjgJ_fXUtPbhWYIW_mWdl?usp=sharing

[amitchandak]

@Nimai123 , Create a rolling formula that checks distinctcount of month year in fact table <=12, if so do not show data.