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 ,
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,
thanks for taking the time to reply me.
I have edit the original post with the data you can copy.
so the logic is basically each event A, B will have it's own start of week number until it finishes
for example event A can have X number of weeks however it starts always with 1 based on OpenDate
same for event B and so on...
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?
OpenDate is always the start point so the week should start counting from OpenDate
Calculating the number of days between two dates is fairly easy but you still did not mention how to calculate for the week. For example, there are 56 days from 1/1 to 25/2 which translates to roughly 8 weeks (56 days/7 days in a week) but your sample result shows 5.
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 startOk. 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]