topic Re: Cumulative Total not working correctly for blank values (based on Ranks) in DAX Commands and Tips

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

Anonymous — Tue, 27 Apr 2021 06:15:45 GMT

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

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

amitchandak — Tue, 27 Apr 2021 06:45:58 GMT

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)
)
)

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

Anonymous — Tue, 27 Apr 2021 06:54:44 GMT

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,

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

amitchandak — Tue, 27 Apr 2021 10:53:08 GMT

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.

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

Anonymous — Tue, 27 Apr 2021 14:40:22 GMT

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.

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

Anonymous — Wed, 28 Apr 2021 05:27:17 GMT

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.