Hello Experts,
I have attached a sample data File Link of what i want to achieve. This is not the exact data but i just mocked up this sample but it is the same logic i am planning to achieve
You will see a the brief in the last sheet (SQL Equivalent). What i want is a DAX implementation like below between the two tables:
SELECT top 5000 d.DateKey,s.DateKey AS SalesDate,s.DeliveryDate,storekey,productkey,salesamount
,CASE
WHEN d.datekey between s.datekey and ISNULL(DeliveryDate,"9999-12-30") THEN 1
WHEN d.datekey < DeliveryDate then 1
else 0
end as DeliveryInDate
FROM [dbo].[tblDimDate] AS D
INNER JOIN [dbo].[tblSales] AS S
ON (D.DateKey >=S.DateKey
OR (S.DateKey is null AND D.DateKey >=S.DateKey) )
1st You will notice that each date in the DimDate table is joined to every date in the Sales table provided it is AFTER the sales date OR This is the condition
ON (D.DateKey >=S.DateKey
OR (S.DateKey is null AND D.DateKey >=S.DateKey) )
2nd My challenge is doing this between the two tables in DAX
3rd Eventually the aim is to create a measure in DAX that will be the sum of The field DeliveryInDate
I used the TOP 5000 to simply limit the records. Also, dont worry so much about the detailed CASE statements and the joins. The whole point is how to create that "cartesian" join condition between two tables and derive the DeliveryInDate measure. Hope I am clear and please let me know if any questions
Thanks
