Cumulative sum of a measure over two dimensions with missing values

[16511151454]

Hi,

I'm trying to create a cumulative sum of a measure over two dimensions and the measure has some missing values.

I have successfully created a simple cumulative sum over one dimension but with some missing values it fails.

The data:

Period	Partner	Category	Amount
2021 Q1	James	Payment	12
2021 Q2	James	Payment	14
2021 Q4	James	Payment	23
2021 Q4	James	Observation	15
2022 Q2	James	Payment	8
2021 Q1	Nelly	Payment	8
2021 Q2	Nelly	Payment	15
2021 Q2	Nelly	Observation	23
2021 Q3	Nelly	Payment	62
2021 Q3	Nelly	Observation	45
2021 Q4	Nelly	Payment	15
2022 Q1	Nelly	Payment	48
2022 Q1	Nelly	Observation	45
2022 Q2	Nelly	Payment	12
2021 Q1	Mark	Payment	53
2021 Q1	Mark	Observation	74
2021 Q2	Mark	Payment	58
2021 Q2	Mark	Observation	55
2021 Q4	Mark	Payment	65
2021 Q4	Mark	Observation	23
2022 Q1	Mark	Payment	22
2022 Q1	Mark	Observation	56
2022 Q2	Mark	Payment	58
2022 Q2	Mark	Observation	45

The measure:

Payment = CALCULATE(SUM('Table'[Amount]), FILTER('Table', 'Table'[Category] = "Payment"))

Cumulative payment over the periods work:

Cumulative Payment =

    CALCULATE(

        [Payment],

        FILTER(

            ALLSELECTED('Table'),

            'Table'[Period] <= MAX('Table'[Period])

        ),

        VALUES('Table'[Partner])

    )

 As you can see there are some missing values (e.g., James does not have a payment in 2021Q3):

The chart by Period and Partner is wrong:

How can I create a measure which fills the blanks? I'd like to obtain the following:

Thanks a lot for the help!

[Anonymous]

Hi @16511151454 ,

I think the cause of the problem is that "James" has no record of "2021 Q3" in your metadata, which results in blank lines, and blank lines are not calculated. I recommend creating a new table and adding custom columns. Here is my method for your reference.

Custom table.

Table 2 = CROSSJOIN(VALUES('Table'[Period]),VALUES('Table'[Partner]))

Custom column.

Amount = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Partner]=EARLIER('Table 2'[Partner])&&'Table'[Period]<=EARLIER('Table 2'[Period])&&'Table'[Category]="Payment"))

Best regards,

Mengmeng Li

[parry2k]

@16511151454 

As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist

Having said that in DAX expression use the date column from the newly added date dimension and also to visualize the data, use the quarter column from the date table. It will fill in the missing period data.

[Kedar_Pande]

Update your existing measure:

Cumulative Payment =
VAR CurrentPartner = SELECTEDVALUE('Table'[Partner])
VAR CurrentPeriod = MAX('Table'[Period])
RETURN
CALCULATE(
SUMX(
VALUES('Table'[Period]),
[Payment]
),
FILTER(
ALLSELECTED('Table'),
'Table'[Period] <= CurrentPeriod && 
'Table'[Partner] = CurrentPartner
)
)

You can create a total measure that sums across partners:

Total Cumulative Payment =
SUMX(
VALUES('Table'[Partner]),
[Cumulative Payment]
)

[16511151454]

Thanks for looking into it.

Unfortunately it doesn't change the result.

I'm thinking there should be a way to dynamically create [Payment] values of 0 where it does not exist for a given combination of [Partner] and [Period]...

[OktayPamuk80]

Hi,

Try this out (using Period as filtering):

Cumulative Payment =
CALCULATE(
sum(financials[ Sales]),
'calendar'[Month] <= SELECTEDVALUE('calendar'[Month])
)

Regards,

Oktay

If it helps, appreciate for Kudos and mark as solution 🙏🏻!!!

[16511151454]

Thanks for the proposal but it does not work. Moreover SUM() cannot work on a measure and I do not have a table named 'calendar', neither can I create one.

[OktayPamuk80]

On the table view, where you can view data, you csn create a calendar using the calendar(startdate, enddate).

The Start and end date would be min and max of your existing date field in the table. Therefore :

Calendar = calendar(min(Date), max(Date))

Afterwards, you can connect it in the modeling view.