There are 4 tables i.e calender,customer,pricelistheader,pricelistdetail.
pricelistheader table is linked to customer table i.e one customer will have one pricelist and then one pricelist will have multiple details in the pricelistdetail table(nothing but multiple SKU's). on top this for each sku there might be multiple records in the line item out of which one will be active at a given point of time according to start_date and end_date.
for more clarification let's see the below query
select * from pricelistdetail where product id = 'xvsdf' and pricelistid = 'cvnjd'
lets suppouse there are 3 entries for above query
id product id pricelist id Start_date End_date Tax-1 Tax-2
1001 xvsdf cvnjd 30-08-2019 25-12-2020 250 59
1002 xvsdf cvnjd 26-12-2020 15-09-2021 225 18
100 xvsdf cvnjd 16-09-2021 30-12-2022 110 22
out of 3 entries only one current active record should be taken. if we directly take any attribute or column from the pricelistdetail table it is fetching all records instead of current record.
Note : There is no relationship between the calender table and pricelist table.
Our Requirement is for the respective calender date, for the respective sku, for the respective customer what will be the pricelist detaile active record. (i.e Tax-1 and Tax-2 should return only active record should not fetch all the records)
customer Number sku Tax-1 Tax-2
12311 productA 123 220
12322 productB 124 222
12333 productC 125 224
Please help me to write dax for getting active Records for Tax-1 and Tax-2 from the pricelistdetailed table.
Thank You.
