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).
hi DataFab2023 ,
I understand that the week count is to be calculated for each even but which columns are used for calculations? Is just OpenDate vs SalesDate in the same row or need to access the previous SalesDate? Once the number of days has been calculated, how do you then translate that to a week count?
Sorry the I've correct the sample as it was confusing before.
So for for that row the sample is desired result as 8
Because the event A OpenDate is 01/01/2023 and SaleDate is 25/03/2023 therefore the week number starting on 01/01/2023 until 25/03/2023 is 8
We can easily divide the number of days by 7 however how do you number then on sequence to determine week1, week2, etc based on OpenDate start
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!