Dynamic Measure

[RG010]

Hello All,

I am trying to create a measure in DAX which will give me a progression of backlog by day, week, month and year. Please refer to sample data which mimics my production data. I have 4 columns - Order number, order value, requested date and actual delivery date. An order is considered backlogged if its actual delivery date is not before or equal to requested date. For eg, if the requested date was Aug 12 and Actual del date was Sept 12 or blank (meaning not yet delivered)- The order value will not be counted as backlog until Aug 12. However, it should start to appear as backlog starting Aug 13 until its actual del date. If the requested del date is greater than today, it will not be counted as backlog. 

I am looking to get an output as a table with 2 columns - Column 1 will be dates from Jan 1 to Dec 31 (or until today's date) and Column 2 will be Backlog amount for each day depending on the logic described above. 

Order #	Order USD	Req Del Date	Actual Del Date
991100	500	10/28/2022	10/30/2022
991101	1000	9/21/2022	9/30/2022
991102	200	8/21/2022	9/15/2022
991103	300	5/12/2022	7/12/2022
991104	800	10/12/2022	11/5/2022
991105	530	7/5/2022	7/4/2022
991106	520	4/12/2022	4/16/2022
991107	510	5/11/2022	7/11/2022
991108	500	10/5/2022	10/4/2022
991109	490	10/5/2022	11/5/2022
991110	480	1/2/2022	2/21/2022
991111	470	12/20/2022
991112	460	4/10/2022	4/22/2022
991113	450	11/4/2022	11/2/2022
991114	440	9/2/2022	9/27/2022
991115	430	1/21/2022	6/20/2022
991116	420	3/7/2022	7/7/2022
991117	410	11/5/2022
991118	400	12/3/2022
991119	390	9/12/2022	10/22/2022
991120	380	8/23/2022	9/21/2022

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Backlog amount(USD) measure: = 
CALCULATE (
    SUM ( Data[Order USD] ),
    FILTER (
        Data,
        Data[Req Del Date] < MAX ( 'Calendar'[Date] )
            && OR (
                Data[Actual Del Date] > MIN ( 'Calendar'[Date] ),
                Data[Actual Del Date] = BLANK ()
            )
    )
)

Backlog ordercount measure: = 
CALCULATE (
    COUNTROWS( Data ),
    FILTER (
        Data,
        Data[Req Del Date] < MAX ( 'Calendar'[Date] )
            && OR (
                Data[Actual Del Date] > MIN ( 'Calendar'[Date] ),
                Data[Actual Del Date] = BLANK ()
            )
    )
)

[RG010]

Thanks @Jihwan_Kim . This solution works best if both requested date and actual dates are in the same table. However, my actual data is structured in 3 different tables - first table has Order number and Value, sencond has order number and Requested date and Third has order number and Actual date. All theree and connected using order number field. In this scenario the filter does not seem to work. Any suggestions to overcome this? Also, is it possible to do this without involving an external calendar table?