Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |