cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fechagregoriana
New Member

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

 

storestart_dateend_date
118/03/202128/03/2021
215/04/202125/04/2021
320/05/202130/05/2021
417/06/202127/06/2021

 

storedatesalesprevious_28dduring_eventnext_28d
116/03/202155100
120/03/202178010
102/04/202146001
212/04/202143100
220/04/202122010
206/05/202131001
315/05/202190100
325/05/202144010
304/06/202156001
411/06/202143100
420/06/20217010
403/07/202158001

 

Thank you all in advance.

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

Hi, @fechagregoriana 

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)

117.png

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)

118.png

 

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.

View solution in original post

1 REPLY 1
v-easonf-msft
Community Support
Community Support

Hi, @fechagregoriana 

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)

117.png

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)

118.png

 

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.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.