Grouping based on date added

[redhughes]

Hi,

We run a report on a daily basis to look at how many places were booked for our events. The report is always for the past calendar week, the current week and 3 weeks ahead (e.g. today I ran it for 22 Jul - 25 Aug 2019). There is always at least one event per day. The date from this report is then imported into our database with a date/time stamp ("Date/time added").

What I'd like to create in PowerBI is a column that shows me the "week number" for each line. By "week number" I mean for example: last week = 1, current week = 2, next week = 3, week after that = 4... Obviously each time we import new data, there will be weeks 1-5 but each week "current week" will mean something different. For instance (data below) on 23 Jul 2019 "next week" was week commencing 29 Jul 2019, but on 30 Jul 2019 "next week" was w/c 5 Aug 2019. In both cases they'd be week 3 though, and those are the numbers I'm after.

What we're aiming for is a possibility of visuals that will show us % of seats booked for (example) "next week" over time. This way we will know if we're getting better at filling them in advance.

In Excel I could get away with using MAXIFS formulas, compare those to event dates for each line and come up with the number, but I have no idea how to get around it in PowerBI...

Many thanks for all your ideas!

Date added	Event date	Seats booked	Total seats	???
23/07/2019	15/07/2019	100	100	1
23/07/2019	16/07/2019	100	100	1
23/07/2019	17/07/2019	100	100	1
23/07/2019	18/07/2019	100	100	1
23/07/2019	19/07/2019	100	100	1
23/07/2019	20/07/2019	100	100	1
23/07/2019	21/07/2019	100	100	1
23/07/2019	22/07/2019	95	100	2
23/07/2019	23/07/2019	95	100	2
23/07/2019	24/07/2019	95	100	2
23/07/2019	25/07/2019	95	100	2
23/07/2019	26/07/2019	95	100	2
23/07/2019	27/07/2019	95	100	2
23/07/2019	28/07/2019	95	100	2
23/07/2019	29/07/2019	90	100	3
23/07/2019	30/07/2019	90	100	3
23/07/2019	31/07/2019	90	100	3
23/07/2019	01/08/2019	90	100	3
23/07/2019	02/08/2019	90	100	3
23/07/2019	03/08/2019	90	100	3
23/07/2019	04/08/2019	90	100	3
30/07/2019	22/07/2019	100	100	1
30/07/2019	23/07/2019	100	100	1
30/07/2019	24/07/2019	100	100	1
30/07/2019	25/07/2019	100	100	1
30/07/2019	26/07/2019	100	100	1
30/07/2019	27/07/2019	100	100	1
30/07/2019	28/07/2019	100	100	1
30/07/2019	29/07/2019	95	100	2
30/07/2019	30/07/2019	95	100	2
30/07/2019	31/07/2019	95	100	2
30/07/2019	01/08/2019	95	100	2
30/07/2019	02/08/2019	95	100	2
30/07/2019	03/08/2019	95	100	2
30/07/2019	04/08/2019	95	100	2
30/07/2019	05/08/2019	90	100	3
30/07/2019	06/08/2019	90	100	3
30/07/2019	07/08/2019	90	100	3
30/07/2019	08/08/2019	90	100	3
30/07/2019	09/08/2019	90	100	3
30/07/2019	10/08/2019	90	100	3
30/07/2019	11/08/2019	90	100	3

[dax]

Hi redhughes,

I am not clear about your requirement, did you want to create a column to show week status, and calculate based on it?

If so, you could use expression like below to create a column

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldQ7CsQwDATQu7gORB//dJaQ+19jt9pYm0SMGoHgNbaGOY4iutPYhdjKVritC9E1y7n9256wI2FnwhpuhRKWE1aWxdqPygNVnFacNpx2nA6cTpyuB7PrX/VOlXDKMKUvnSAVnCpOa0Tdo12y7in0VhO2JmzcCN7GjeBt3Ajexo3grUVZdNQHLKYMUx+wmApOFacVpw3OInWcDpxOnBpMmXD6XgfnBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Date added" = _t, #"Event date" = _t, #"Seats booked" = _t, #"Total seats" = _t, #"???" = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date added", type text}, {"Event date", type text}, {"Seats booked", Int64.Type}, {"Total seats", Int64.Type}, {"???", Int64.Type}}),

    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Event date", type date}}, "co-FR"),

    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "weekname", each if(Date.IsInPreviousNWeeks([Event date],1)) then "last week" else if (Date.IsInPreviousNWeeks([Event date],2)) then "laset week -1" else if (Date.IsInNextNWeeks([Event date], 1)) then "next week" else if (Date.IsInNextNWeeks([Event date], 2)) then "next week+1"  else if (Date.IsInCurrentWeek([Event date])) then "current" else "other")

in

    #"Added Custom"

Then create a measure like below

Measure =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Seats booked] ),
        ALLEXCEPT ( 'Table', 'Table'[Date added], 'Table'[weekname] )
    ),
    CALCULATE (
        SUM ( 'Table'[Total seats] ),
        ALLEXCEPT ( 'Table', 'Table'[Date added], 'Table'[weekname] )
    )
)

Then you will get result like below

If this is not what you want, please correct me and inform me more detailed information(such as your sample data and your expecting output), then I will help you more correctly

Best Regards,

Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[redhughes]

thank you! I will try it out next week, and will let you know.

[dax]

Hi redhughes,

Did this help you solve your issue? If so and if you'd like to, you could mark corresponding post as answer or share your solutions. That way, people who in this forum and have similar issue will benefit from it.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[redhughes]

actually, I have done some testing and the formulas you suggested don't work as intended.

in your screenshot, all lines that in in the second column have 28 Jul-3 Aug are "current" in fifth column. that's not true, because when data was extracted on 23 Jul (first column) "current" week was 22-28 Jul, and when extraction data was 30 Jul "current" week was 29 Jul - 4 Aug.

It seems your formulas are based on live today date/time, and will change with time. I don't them to change. When I run data on 23 Jul, 22-28 Jul should be "current" and should stay "current" even after two weeks.

hope you understand what I'm trying to say, and you are able to help further?