cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pnsujith Frequent Visitor
Frequent Visitor

Rolling Quarters distinct count of documents

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.

DocumentPosting DateQuarterVendorAmountClearing Doc
D5347224349620191ABC442C311101621
D4867294349720191CVB850C237968269
D8221024352720191XYZ221C630671866
D8390654313420181ABC333C311101621
D5442934277020171ABC348C856864831
D1355514350120191POK94C218481646
D9527594313720181CVB65C965730315
D9267154277320171CVB928C664036872
D4058234350420191SDF780C102987470
D1288994354320191JKL206C726804533
D5096914314120181XYZ901C751616187
D8212724277720171XYZ282C676122956
D6675514314320181RTH101C842366235
D8308504314420181BHU948C626485659

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

D5347224349620191ABC442C311101621
D4867294349720191CVB850C237968269
D8221024352720191XYZ221C630671866
D1355514350120191POK94C218481646
D4058234350420191SDF780C102987470
D1288994354320191JKL206C726804533

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

D9527594313720181CVB65C965730315
D5096914314120181XYZ901C751616187

so the final result should be

D5347224349620191ABC442C311101621
D4867294349720191CVB850C237968269
D8221024352720191XYZ221C630671866
D1355514350120191POK94C218481646
D4058234350420191SDF780C102987470
D1288994354320191JKL206C726804533
D9527594313720181CVB65C965730315
D5096914314120181XYZ901C751616187

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.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Rolling Quarters distinct count of documents

Hi @pnsujith ,

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

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
pnsujith Frequent Visitor
Frequent Visitor

Re: Rolling Quarters distinct count of documents

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.