Each record of date from start and end_date by Dax

[mmh]

Hi,

There is a fact table with contract date as starDate and EndDate as

Id	investment	ResidualValue	start_date	End_date
21	100	50	1/1/2021	04/06/23
22	300	60	1/1/2022	1/1/2023

Need to calculated Exposure for every day inbetween (star and endate). For this measure i have created a custom colum for each record (in between start and end date), but problem the table become huge for each record n taking long time load. So is there any way create each record of date in Dax or measure exposure from date dimension. the calculation (exposure is )= remaining days of contract /total duration * (investment-residual value). Where total duratio is just subtraction of end_date and start_date and Remining days is contact is substraction of each_date (that would like create as calculate column or measures in Dax ) and start_date.

Thanks in advance.

BR

mmh

[mmh]

Hi John,

Thanks. It worked exactly as per my expectation

[johnt75 · Accepted solution]

Try

Exposure =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Exposure =
    CALCULATE (
        SUMX (
            'Table',
            VAR StartDate = 'Table'[start date]
            VAR EndDate = 'Table'[end date]
            VAR Investment = 'Table'[investment]
            VAR ResidualValue = 'Table'[residual value]
            VAR TotalDuration =
                INT ( EndDate - StartDate )
            VAR RemainingDays =
                INT ( EndDate - MaxDate )
            RETURN
                DIVIDE ( RemainingDays, TotalDuration ) * ( Investment - ResidualValue )
        ),
        'Table'[start date] <= MaxDate
            && 'Table'[End date] >= MaxDate
    )
RETURN
    Exposure