Forum Discussion
Creating Flag Columns by Date Period
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.
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]-28Next date = 'Table 2'[End Date]+282.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.
1 Reply
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]-28Next date = 'Table 2'[End Date]+282.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.