Record Growth for QA - in Power BI

[Anonymous]

I am looking for help in understanding the total unique record count, by day for each day.   I think this falls into a category of "slowly changing dimensions", but each day my record count should grow.

This is a function of understanding the how many unique records were in the database on any given date, or between dates or over time.

For instance, if I gave a person one jellybean for the the first 6 days of a week, but on Day 7 I gave him two.  The data would show "8 Jellybeans" on Day 7, and when trended it would show the following.

Day 1 - 1

Day 2 - 2

Day 3 - 3

Day 4 - 4

Day 5 - 5

Day 6 - 6

Day 7-  8

Having this data answers questions like:

• How many unique jelly beand did I have on Day 4?  Answer: 4
• How many uniques jelly beans did I have since the start?  Answer: 8
• What is my day-over-day growth between days 5 and 7:  Answer: 3

Thanks in advance.

[v-xjiin-msft · Accepted solution]

Hi @Anonymous,

In your scenario, did you mean cumulative sum?

Please refer to following sample:

Source table is like:

Then use RANKX() function to create an order column and based on this order do a cumulative sum in a calculated column:

Rank =
RANKX ( 'jelly bean', 'jelly bean'[Day],, ASC, DENSE )


Total beans =
CALCULATE (
    SUM ( 'jelly bean'[Number] ),
    FILTER (
        ALL ( 'jelly bean' ),
        'jelly bean'[Rank] <= EARLIER ( 'jelly bean'[Rank] )
    )
)

Thanks,
Xi Jin.