Calendar and column with last period from date

[Denis_Slav]

Hello, 

How I can create column in calendar table, where will be marked as "0/1" dstes, which will be in range between DT_Start and DT_End. Where:

DT_End = Last date in dataset table

DT_Start = DT_End with lag for X period (days, month)?

Sample of file 

[V-pazhen-msft · Accepted solution]

@Denis_Slav 

Try create this column in the calendar table:

Column = IF(DIC_Calendar[DT_DATE]>= DS_Value[DT_Maxx] -3 && [DT_DATE] <=DS_Value[DT_Maxx],1,0)

For week lags, try:

Column = IF(DIC_Calendar[DT_DATE]>= DS_Value[DT_Maxx] -3 && [DT_DATE] <=DS_Value[DT_Maxx],1,0)

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[V-pazhen-msft]

@Denis_Slav 

Still not clear for me, could you provide the expected output in a table. For now, you need to specify the period unit(Month, Day or Week). 

Regards
Paul

[Denis_Slav]

@V-pazhen-msft Try to explain on "day" lag.
What I have:

1) Generated calendar

DT_Date = {01/01/2019..31/12/2020}

2) Data set with last date

DT_LastDate = 07/05/2020

What I woul like:
1) Mark in Calendar table dates in lag=3 days from DT_LastDate:

If 
 ([DT_Date] > ([DT_Last_date] - 3)) && ([DT_Date] <= [DT_LastDate])
Then 1
Else 0

What I would like in output:
Table 'Calendar' with custom column with check if date in range

DT_Date	CHK
01/05/2020
02/05/2020
03/05/2020
04/05/2020	1
05/05/2020	1
06/05/2020	1
07/05/2020	1
08/05/2020
09/05/2020

[parry2k]

@Denis_Slav not fully sure what you mean lag of X? What is X?

[Denis_Slav]

@parry2k I mean change date to several periods. For example I have date 01/04/2020 and lag for "1 month" give me 01/03/2020, or lag "2 week" give me 18/03/2020.