cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arkojj
Frequent Visitor

Previous N week data

Hi,

 

I have a table that captures registrations for events and this table has 2 date fields - RegistrationDate and EventStartDate.

 

I need to calculate the cumulative No. of registrations for each event from the previous 52 weeks until the event starts (illustrated below).

ESD = Event Start Date

 

EventName ESD-52weekESD-51weekESD-50week...ESD-2weekESD-1weekESD-0week 
Event1047...152044 
Event27727...354254 
Event3152133...354766 

 

The last numbers (44, 54, 66) are the total registrations for the respective events.

Calculation on the registrations need to be on RegistrationDate and reaches its max at EventStartDate.

A tabular representation would work fine for me.

 

Any ideas on how this can be achieved, please?

 

Thanks in advance!

1 ACCEPTED SOLUTION
mahoneypat
Super User IV
Super User IV

The simplest way to do this would be just add a calculated column to your table with this formula

 

RegistrationWeek = DATEDIFF(Table[EventStartDate], Table[RegistrationDate], WEEK)

 

That will give you # of weeks before the event (-1,-42, etc.).  You can then make a matrix with Event on the rows and the new column on the Columns, and use a running total measure like this

Cumulative Registrations =
VAR __thisweek =
SELECTEDVALUE ( Table[RegistrationWeek] )
RETURN
CALCULATE (
COUNTROWS ( Table ),
ALL ( Table[RegistrationWeek] ),
Table[RegistrationWeek] <= __thisweek
)

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

8 REPLIES 8
mahoneypat
Super User IV
Super User IV

The simplest way to do this would be just add a calculated column to your table with this formula

 

RegistrationWeek = DATEDIFF(Table[EventStartDate], Table[RegistrationDate], WEEK)

 

That will give you # of weeks before the event (-1,-42, etc.).  You can then make a matrix with Event on the rows and the new column on the Columns, and use a running total measure like this

Cumulative Registrations =
VAR __thisweek =
SELECTEDVALUE ( Table[RegistrationWeek] )
RETURN
CALCULATE (
COUNTROWS ( Table ),
ALL ( Table[RegistrationWeek] ),
Table[RegistrationWeek] <= __thisweek
)

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Hi @mahoneypat ,

 

As per your suggestion, this is what I could achieve.
Screen.jpg

There are a couple more things that I would like to know :

 

Question 1 - How can I populate the empty cells with the same previous value? (For eg - the highlighted part should contain 150 for -3, -2, -1 and 0 weeks)

 

Question 2 - If I select only events that has gaps, even the header (RegistrationWeek) skips the columns that has no data. How can I keep this static to always show 0 to -52 even if there is no data? (this is related to Question 1 as there is no data to show in that column)

The reason you are seeing blanks is that there are no rows for that week, so there is not a RegistrationWeek value to go in the first part with SELECTEDVALUE().  To get around that there are two approaches that could work.  I recommend the second one:

 

1. Add blank rows in the query editor to fill in the missing dates/weeks (e.g., make a full list of dates/weeks, convert to table, and then merge your data into it for each customer)

 

2. Make a disconnected table of regisration week numbers (e.g., with GENERATESERIES(-42,1,1) or something like that and use that in your columns.  Then modify the measure expression to get the SELECTEDVALUE() of that column.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Went by the second approach and that helped... Thank you! 😊

@mahoneypat 

This is excellent Pat... Thanks a ton.

Your solution is just about right. I can modify the rest as per the requirement.

stevedep
Super User I
Super User I

So looking back 52 weeks but not before the registration date, or will there not be any registratios anyway before the registration date?

harshnathani
Super User III
Super User III

Hi @harshnathani ,

 

The criticality of my requirement is that we have 2 separate date fields and the calculation is based on both.

No. of registrations are being calculated on the RegistrationDate, whereas the offset needs to be calculated on the EventStartDate.

 

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!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors