Creating Flag Columns by Date Period

[Anonymous]

Hello everyone, I'm trying to create three measures:

- Sales 28 days previous event

- Sales during event

- Sales 28 days after event

To do that I've two tables, and I'm trying to create three columns to flag each time period.

In dark blue you can see the original tables, and in red you can see the columns I want to add.

Any suggestion on how to:

-Create the measures without the flags

-Create the flag columns so then I can create de measures

store	start_date	end_date
1	18/03/2021	28/03/2021
2	15/04/2021	25/04/2021
3	20/05/2021	30/05/2021
4	17/06/2021	27/06/2021

store	date	sales	previous_28d	during_event	next_28d
1	16/03/2021	55	1	0	0
1	20/03/2021	78	0	1	0
1	02/04/2021	46	0	0	1
2	12/04/2021	43	1	0	0
2	20/04/2021	22	0	1	0
2	06/05/2021	31	0	0	1
3	15/05/2021	90	1	0	0
3	25/05/2021	44	0	1	0
3	04/06/2021	56	0	0	1
4	11/06/2021	43	1	0	0
4	20/06/2021	7	0	1	0
4	03/07/2021	58	0	0	1

Thank you all in advance.

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

You can follow steps as below:

1.add calculated columns in table2:

Start Date = RELATED('Table 1'[start_date])

End Date = RELATED('Table 1'[end_date]) 

Previous  date = 'Table 2'[Start Date]-28

Next  date = 'Table 2'[End Date]+28

2.Create the flag columns:

previous_28d = IF('Table 2'[date]>'Table 2'[Previous  date]&&'Table 2'[date]<'Table 2'[Start Date],1,0)

during event = IF('Table 2'[date]>'Table 2'[Start Date]&&'Table 2'[date]<'Table 2'[End Date],1,0)

next_28d = IF('Table 2'[date]>'Table 2'[End Date]&&'Table 2'[date]<'Table 2'[Next  date],1,0)

3.Create the measures with the flags

Sales 28 days previous event = CALCULATE(SUM('Table 2'[sales]),'Table 2'[previous_28d]=1)

Sales during event = CALCULATE(SUM('Table 2'[sales]),'Table 2'[during event]=1)

Sales 28 days after event = CALCULATE(SUM('Table 2'[sales]),'Table 2'[next_28d]=1)

Please check my sample file for more details.

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