Lookup dates dynamically for each row of data from a seperate table within a measure

[Anonymous]

Hi Everyone

I have a problem and could someone please help out. I have two tables, a Sales Table showing the dailysale of outlets and an Agrement Table showing the Validity start and end date for each outlet. The outlet Code/Name is the common field connecting both tables and acting as the relationship.

The problem is that Sales can be done to outlets even after their agreement is expired and so i want to split my total sales to each outlet by what is valid (Within the agreement start and end Date) and what sales have been done as miscouduct (Sales to outlet beyond agreement timeline)

Sales Table	(YTD SFA Table)
Outlet	Day	Sale
A	01-01-19	10
A	02-02-19	75
A	03-03-19	20
A	08-08-19	90
B	05-05-19	45
B	06-06-19	35
C	01-01-19	150
C	07-07-19	25

Agreement	( TieUp)
Outlet Table	Valid Start	Valid End	Sale within valid period (Desired results)
A	01-05-18	30-04-19	105
B	01-06-19	01-06-20	35
C	01-01-19	31-12-19	175

I have gonnen there partially with the folloing code:

Sale Between = CALCULATE(SUMX('YTD SFA RD', 'YTD SFA RD'[QTY sold (Only in CS)]) , DATESBETWEEN( 'YTD SFA RD'[Order Confirm Date],

    LOOKUPVALUE(TieUp[Agreement - From] , TieUp[Outlet Code] , MAX( 'YTD SFA RD'[Outlet Code] ) ),

    LOOKUPVALUE(TieUp[Agreement - To] , TieUp[Outlet Code] , MAX( 'YTD SFA RD'[Outlet Code])

)))

I cannot get the lookupvalue function to bring in the Agreement Start date and The Agreement End date form my Agreement Table (Tieup).

The start and end dates will differ from outlet to outlet.

[Anonymous]

@Anonymous 

Hope you have established the relationship between both the table with the Outlet name.

Create an measure with the below DAX

Measure =  CALCULATE( SUM(Table1[Sale]),

FILTER(Table1,Table1[Day]>=RELATED(Table2[Valid Start]) && Table1[Day]<=RELATED(Table2[Valid End])))

Nandri

[Anonymous · Accepted solution]

@Anonymous  Thank you, this worked for me.