topic Re: Each record of date from start and end_date by Dax in DAX Commands and Tips

Each record of date from start and end_date by Dax

mmh — Mon, 20 Feb 2023 11:20:27 GMT

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.

mmh

Re: Each record of date from start and end_date by Dax

johnt75 — Mon, 20 Feb 2023 14:25:23 GMT

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

Re: Each record of date from start and end_date by Dax

mmh — Wed, 22 Feb 2023 10:19:56 GMT

Hi John,

Thanks. It worked exactly as per my expectation