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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Nick2385
New Member

DAX Count of Cumulative revenue by ID each month for a selected period

Hello everyone,

I've been struggling with this for 3 weeks and can't seem to find a solution that works. 
Here is my problem. 
I have 2 tables, one with Monthly revenue by ID and Date, and one Calendar table (with a relationship between both).
I created a measure to have a cumulative revenue for the past 12 months each month by ID. 
Now I need a count for each ID each month (1 row = 1) and another onefor each ID whose Cumulative Revenue is not 0 (each row = 1 or 0). 
The problem is there is no row in my fact table when there is no revenue that month, but the cumulative shows a row in the table visual and I need to count that (that's where my problem is - it returns a blank).
Here is a table that shows what I get and what my expected result is: 

 

IDMonthMonthly RevenueCumulative Rev.Count ObtainedCount Expected
101/24101011
102/24No Data10Blank1
103/24102011
104/24103011
201/24101011
202/24No Data10Blank1
203/24No Data10Blank1
204/24102011

 

The objective is to divide
Count of ID with a cumulative Revenue <> 0 for a selected period

by

Count of ID for a selected period. 

I hope it's clear, this is not really easy to explain. 
Unfortunately I can't share the pibx since this si confidential information. 

Thanks a lot everyone! 

1 ACCEPTED SOLUTION
v-mengmli-msft
Community Support
Community Support

Hi @Nick2385 ,

 

This is because you are not clearing the row context and the calculation is only performed on the current row. You can use the ALL function to clear this context. If you also want to calculate the accumulation by ID, you can use the following DAX.

Cumulative sum = 
VAR _cumul=
CALCULATE (SUM(
    'Revenue'[Revenue]),ALL(Revenue),
    DATESINPERIOD('CalendarTable'[Date], MAX(CalendarTable[Date]), -12, MONTH
    ),Revenue[ID]=MAX(Revenue[ID])
)
RETURN
_cumul

vmengmlimsft_0-1736905078608.png

 

 

 

 

 

Best regards,

Mengmeng Li

View solution in original post

7 REPLIES 7
v-mengmli-msft
Community Support
Community Support

Hi @Nick2385 ,

 

This is because you are not clearing the row context and the calculation is only performed on the current row. You can use the ALL function to clear this context. If you also want to calculate the accumulation by ID, you can use the following DAX.

Cumulative sum = 
VAR _cumul=
CALCULATE (SUM(
    'Revenue'[Revenue]),ALL(Revenue),
    DATESINPERIOD('CalendarTable'[Date], MAX(CalendarTable[Date]), -12, MONTH
    ),Revenue[ID]=MAX(Revenue[ID])
)
RETURN
_cumul

vmengmlimsft_0-1736905078608.png

 

 

 

 

 

Best regards,

Mengmeng Li

Nick2385
New Member

Hi everyone,
I appologize for the delay.
I can't give you a proper pbix but I can show you what my tables look like. 

 

-Monthly revenue table:-

DateIDRevenue
01/2024110
03/2024110
04/2024110
01/2024210
04/2024210

 

-A Calendar table-

 

-DAX Formula -
Cumulative sum:

VAR _cumul=
CALCULATE (SUM(
    Table[Revenue]),
        DATESINPERIOD('Calendar)'[Date], MAX('Calendar'[Date]), -12, MONTH
    )
)
RETURN
_cumul
 
This leads to my issue mentionned in thetopic since in the table I have no rows for certain months. 
But they need to be counted because the cumulative sum adds revenue values for the missing months in the table.
 
I hope it makes more sense. 

Thanks everyone!
Nick2385
New Member

Hi everyone,
I appologize for the delay.
I can't give you a proper pbix but I can show you what my tables look like. 

 

-Monthly revenue table:-

DateIDRevenue
01/2024110
03/2024110
04/2024110
01/2024210
04/2024210

 

-A Calendar table-

 

-DAX Formula -
Cumulative sum:

VAR _cumul=
CALCULATE (SUM(
    Table[Revenue]),
        DATESINPERIOD('Calendar)'[Date], MAX('Calendar'[Date]), -12, MONTH
    )
)
RETURN
_cumul
 
This leads to my issue mentionned in thetopic since in the table I have no rows for certain months. 
But they need to be counted because the cumulative sum adds revenue values for the missing months in the table.
 
I hope it makes more sense. 

Thanks everyone!
powerbiexpert22
Post Prodigy
Post Prodigy

Hi @Nick2385 ,

can you share some sample data or pbix?

v-mengmli-msft
Community Support
Community Support

Hi @Nick2385 ,

 

Have you tried this? This may be helpful to you.

Count = COUNTROWS('Table')

vmengmlimsft_1-1736303658035.png

Currently, I am unable to reproduce the same issue, if the above dax doesn't work, please provide more details like DAX of Count Obtained, field of visual. The best thing is to provide a demo with the same problem (link to this public file), this will help us to solve the problem better.

 

 

Best regards,

Mengmeng Li

ryan_mayu
Super User
Super User

could you pls provide the data of Monthly revenue table?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




lbendlin
Super User
Super User

I have 2 tables, one with Monthly revenue by ID and Date, and one Calendar table (with a relationship between both).

To report on things that are not there you need to use disconnected tables and/or crossjoins

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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