Rolling Quarters distinct count of documents

[Anonymous]

Hi Experts, I'm having a requirement, where I need to get all the rows with certain vendor in the current selection of quarter rolling back to last 5 quarters based on selected quarter.

Document	Posting Date	Quarter	Vendor	Amount	Clearing Doc
D534722	43496	20191	ABC	442	C311101621
D486729	43497	20191	CVB	850	C237968269
D822102	43527	20191	XYZ	221	C630671866
D839065	43134	20181	ABC	333	C311101621
D544293	42770	20171	ABC	348	C856864831
D135551	43501	20191	POK	94	C218481646
D952759	43137	20181	CVB	65	C965730315
D926715	42773	20171	CVB	928	C664036872
D405823	43504	20191	SDF	780	C102987470
D128899	43543	20191	JKL	206	C726804533
D509691	43141	20181	XYZ	901	C751616187
D821272	42777	20171	XYZ	282	C676122956
D667551	43143	20181	RTH	101	C842366235
D830850	43144	20181	BHU	948	C626485659

So, when user selects Quarter 20191, from the drop down, it has to fetch rows which are in 20191 as below

D534722	43496	20191	ABC	442	C311101621
D486729	43497	20191	CVB	850	C237968269
D822102	43527	20191	XYZ	221	C630671866
D135551	43501	20191	POK	94	C218481646
D405823	43504	20191	SDF	780	C102987470
D128899	43543	20191	JKL	206	C726804533

and also the ones from last 5 quarters which have same vendors as of the vendors selected in 20191, but different clearing docs

D952759	43137	20181	CVB	65	C965730315
D509691	43141	20181	XYZ	901	C751616187

so the final result should be

D534722	43496	20191	ABC	442	C311101621
D486729	43497	20191	CVB	850	C237968269
D822102	43527	20191	XYZ	221	C630671866
D135551	43501	20191	POK	94	C218481646
D405823	43504	20191	SDF	780	C102987470
D128899	43543	20191	JKL	206	C726804533
D952759	43137	20181	CVB	65	C965730315
D509691	43141	20181	XYZ	901	C751616187

its more like pick all the vendors from the selected quarter look for matches in the previous 5 quarters and do a distinct count of clearing doc on window vendor, if its greater than 2 fetch else dont. I'm unable to do this in DAX.

Please help

Thanks.

[v-piga-msft]

Hi @Anonymous ,

Based on your data sample and your logic, if I undersand it correctly the output should be like below?

D534722	43496	20191	ABC	442	C311101621
D486729	43497	20191	CVB	850	C237968269
D822102	43527	20191	XYZ	221	C630671866
D135551	43501	20191	POK	94	C218481646
D405823	43504	20191	SDF	780	C102987470
D128899	43543	20191	JKL	206	C726804533
D952759	43137	20181	CVB	65	C965730315
D839065	43134	20181	ABC	333	C311101621
D509691	43141	20181	XYZ	901	C751616187

If not, please explain the logic again.

Best Regards,

Cherry

[Anonymous]

Hello,

the row marked with red should not be present as the clearing doc for first row and the one marked red are same.

Except that everything is correct.

However, I think I have figured a way out to do this.

I'm still testing it.

Thanks,

Sujith.