Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

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.
 
amatirs_0-1619504067130.png

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

 

Any help would be highly appreciated.

 

Thankyou

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Hi @amitchandak ,

 

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

 

amatirs_0-1619506158664.png

 

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,

 

amatirs_1-1619506473519.png

 

@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.

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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,

 

amatirs_0-1619534130297.png

 

Thanks in advance.

 

 

Anonymous
Not applicable

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.