Forum Discussion
Dynamic Count of Week between two dimensions
Hi DataFab2023 ,
What is your logic for counting how many weeks there are between two dates? Do you get the difference between the two and divide it by seven? Also, and as always, please post a sample data that we can copy-paste (not an image).
Ok. I've create two calculated column, one to get the numbr of days from OpenDate to SalesDate which you can either round up or down to get to the nearest whole number and two to get the sequence per event based on which SalesDate comes first if that is what you mean.
Week count =
ROUNDUP (
DIVIDE ( DATEDIFF ( 'Table'[OpenDate], 'Table'[SaleDate], DAY ) + 1, 7 ),
0
)
Sequence =
RANKX (
FILTER ( 'Table', 'Table'[Event ] = EARLIER ( 'Table'[Event ] ) ),
'Table'[SaleDate],
,
ASC,
DENSE
)
Sequence Week =
"Week" & 'Table'[Sequence]
Hi Mate,
the weekcount calculation you did is almost everything I needed,
The only thing I didn't know and I just notice is that the Opening will always be on saturday and I need to ignore the Saturday and Sunday and Start counting the week on the first Monday.
Basically ignore the first saturday/sunday of the openning week and start counting the week on monday always until next event
For example:
| Event | OpenDate | Weekday | SaleDate | DesiredResult | |
| A | 7/01/2023 | Saturday Opening Weekend | 7/01/2023 | 0 | |
| A | 7/01/2023 | Sunday Opening Weekend | 8/01/2023 | 0 | |
| A | 1/01/2023 | Sunday | 15/01/2023 | 1 | |
| A | 1/01/2023 | Saturday | 4/02/2023 | 4 | |
| A | 1/01/2023 | Friday | 24/02/2023 | 8 | |
| B | 1/03/2023 | Saturday Opening Weekend | 1/03/2023 | 0 | |
| B | 1/03/2023 | Sunday Opening Weekend | 2/03/2023 | 0 | |
| B | 1/03/2023 | Tuesday | 25/03/2023 | 4 | |
| B | 1/03/2023 | Friday | 5/04/2023 | 5 | |
| B | 1/03/2023 | Tuesday | 30/04/2023 | 9 |
So what I did was to add +2 to the OpenDate and it seems to be working to exception of one single date giving odds numbers but that's very close to what I needed! thanks!