Forum Discussion

bmjacques's avatar
bmjacques
Frequent Visitor
2 years ago
Solved

Cumulative Total by Weeks

I am trying to create a measure to calculate cumulative registrations by weeks before an event takes place. I have a data set with 15 years of meeting registration data that includes the date a person registered and the date of the meeting. I created a calculated column to determine how many weeks before the meeting a person registered. I want to use this to compare how our event registrations from this year compare to previous years (i.e. so far in 2024 we are 24 weeks out from the meeting and have 111 registrations. In 2023 at 24 weeks out we had 101 registrations).

 

Calculated column for "Days Registered" = Table.AddColumn(#"Changed Type3", "Subtraction", each Duration.Days([Date Registered] - [Meeting Date]), Int64.Type)

Calculated column for "Weeks Registered" = Table.AddColumn(#"Inserted Date Subtraction", "Integer-Division", each Number.IntegerDivide([Subtraction], 7), Int64.Type)

 

 

 

I put the data into an Excel pivot table and this is what I want to recreate in PBI with the desired outcome being a chart similar to this:

 

 

 

 2023 2024 
Weeks RegisteredTotalCumulativeTotalCumulative
-4211 0
-41 111
-40 112
-3612 2
-3479 2
-3381768
-321182028
-31624432
-30529537
-291443441
-286491556
-272511066
-2612631985
-25218422107
-24171014111
-23107208 111
-22102310 111
-2145355 111
-20153508 111
-1978586 111
-1839625 111
-1740665 111
-1640705 111
-1533738 111
-1447785 111
-1353838 111
-1288926 111
-111071033 111
-101531186 111
-92421428 111
-81721600 111
-74282028 111
-611773205 111
-52483453 111
-42283681 111
-31603841 111
-2873928 111
-11814109 111
02254334 111
114335 111
214336 111
334339 111
414340 111
614341 111

 

Here is a simplified example of the "Registrations" table:

Meeting NameCancelledYearMeeting DateDate RegisteredDays RegisteredWeeks Registered
2024 San AntonioNo202411/14/20245/27/2024-171-24
2024 San AntonioNo202411/14/20245/27/2024-171-24
2024 San AntonioNo202411/14/20245/23/2024-175-25
2024 San AntonioNo202411/14/20245/16/2024-182-26
2024 San AntonioNo202411/14/20245/9/2024-189-27
2024 San AntonioNo202411/14/20245/2/2024-196-28
2024 San AntonioYes202411/14/20244/21/2024-207-29
2024 San AntonioNo202411/14/20244/17/2024-211-30
2024 San AntonioNo202411/14/20244/11/2024-217-31
2024 San AntonioNo202411/14/20244/4/2024-224-32
2024 San AntonioNo202411/14/20243/26/2024-233-33
2024 San AntonioYes202411/14/20242/7/2024-281-40
2024 San AntonioYes202411/14/20241/31/2024-288-41
2023 St. LouisNo202311/1/202311/28/2023273
2023 St. LouisNo202311/1/202311/20/2023192
2023 St. LouisNo202311/1/202311/13/2023121
2023 St. LouisNo202311/1/202311/1/202300
2023 St. LouisYes202311/1/202310/19/2023-13-1
2023 St. LouisNo202311/1/202310/4/2023-28-4
2023 St. LouisNo202311/1/20239/27/2023-35-5
2023 St. LouisNo202311/1/20239/20/2023-42-6
2023 St. LouisNo202311/1/20239/13/2023-49-7
2023 St. LouisNo202311/1/20238/23/2023-70-10
2023 St. LouisNo202311/1/20237/26/2023-98-14
2023 St. LouisNo202311/1/20235/10/2023-175-25
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi bmjacques 

     

    For your question, here is the method I provided:

     

    Here's some dummy data

     

    "Table"

     

     

    Create measures.

     

    Total = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALL('Table'),
        'Table'[Meeting Name] = MAX('Table'[Meeting Name])
        &&
        'Table'[Weeks Registered] = MAX('Table'[Weeks Registered])
    )
)

     

    Cumulative = 
SUMX(
    FILTER(
        ALL('Table'),
        'Table'[Meeting Name] = MAX('Table'[Meeting Name])
        &&
        'Table'[Days Registered] <= MAX('Table'[Days Registered])
    ),
    'Table'[Total]
)

     

    Here is the result.

     

     

    Regards,

    Nono Chen

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

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi bmjacques 

     

    For your question, here is the method I provided:

     

    Here's some dummy data

     

    "Table"

     

     

    Create measures.

     

    Total = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALL('Table'),
        'Table'[Meeting Name] = MAX('Table'[Meeting Name])
        &&
        'Table'[Weeks Registered] = MAX('Table'[Weeks Registered])
    )
)

     

    Cumulative = 
SUMX(
    FILTER(
        ALL('Table'),
        'Table'[Meeting Name] = MAX('Table'[Meeting Name])
        &&
        'Table'[Days Registered] <= MAX('Table'[Days Registered])
    ),
    'Table'[Total]
)

     

    Here is the result.

     

     

    Regards,

    Nono Chen

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