Cumulative Total not working correctly for blank values (based on Ranks)

[Anonymous]

Hi,

So I have created a Measure (Billing Linearity Rolling) for calculating cumulative total over weeks. 

Billing Linearity Rolling =

CALCULATE(

    [Revenue Forecast for Historical Data],

    FILTER(

        ALLSELECTED('Period table Committed'[Week Committed]),

        ISONORAFTER('Period table Committed'[Week Committed], MAX('Period table Committed'[Week Committed]), DESC)

    )

)

where, Revenue Forecast for Historical Data= 

 DIVIDE(SUM('Billing Linearity Data'[Consulting Amount USD]),CALCULATE(SUM('Billing Linearity Data'[Consulting Amount USD]),ALL('Period table Committed'[Week Committed])))

But the Rolling formula is not working for weeks with Blank Values. All i want is that Blank should reflect previous week's value.

eg= for canada week 9 value should be 39%, and Week 13 should be 100%

Any help would be highly appreciated.

Thankyou

[amitchandak]

@Anonymous , Try with +0

Billing Linearity Rolling =
CALCULATE(
[Revenue Forecast for Historical Data]+0,
FILTER(
ALLSELECTED('Period table Committed'[Week Committed]),
ISONORAFTER('Period table Committed'[Week Committed], MAX('Period table Committed'[Week Committed]), DESC)
)
)

[Anonymous]

Hi @amitchandak ,

After adding +0, Blank values are getting replaced by 0%,

This is not my requirement. I just want the blank values to show cumulative value which should be equal to previous week value. My table should look like below,

[amitchandak]

@Anonymous , 'Period table Committed' is kind of week/ date table and joined to Billing Linearity Data on date or week ?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Hi @amitchandak ,

Yes Period Table is a Calendar Table which is customised. I have used 4-4-5 calendar type.

It is Joined to Billing Linearity table with Date field. And here I am using Week Committed from Period Table to get the cumulative total.

Below is the output that I am getting, and the desired output that i want,

Thanks in advance.

[Anonymous · Accepted solution]

Hi,

I got a solution to my problem. I had to change the Cross Filter Direction to "Single" from "Both" for Many to One Cardinality. And it worked.